Post reply

Denormalized table

  • August 24, 2008 at 12:17 pm

    #211497

    I have a denormalised table with each record representing a projectID. In our projects we have stakeholders who contribute to the project, now the table i have to work with is built with a column for each stakeholder (8 in total), basically bad design, however, I want to build a report where i have in each row the project details and in that same row the stakeholders of the project, without repeating the project details each time a new stakeholder is found. I started off with the following query:-

    select projectID, max(fieldname2), sum(fieldname3), max(stakeholderA) from tablename

    where ......

    group by fieldname

    union

    select projectID, max(fieldname2), sum(fieldname3), max(stakeholder) from tablename

    where ......

    group by fieldname

    however and correctly so the projectID record is repeated in a new row when there is more than one stakeholder for a given projectID as follows:

    projectA, xxxx, yyyy, stakeholderA

    projectA, xxxx, yyyy, stakeholderB

    ....

    I understand that this is the correct behaviour however, I don't want this behaviour, rather I would like something like

    projectA, xxxx, yyyy, stakeholderA

    stakeholderB

    I think this is not possible directly in SQL, however, am not sure, any advice would be much appreciated.

    CHEERS!!

  • August 24, 2008 at 3:20 pm

    #862325

    Think you should read Jeff Moden's latest article

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

    By Jeff Moden, 2008/08/19

    at http://qa.sqlservercentral.com/articles/T-SQL/63681/

    It is a great article on what it appears you want to perform

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • August 24, 2008 at 3:59 pm

    #862330

    Heh... thanks for the plug, BitBucket!

    A way kind of backwards from Cross Tabs is shown in the following article...

    http://qa.sqlservercentral.com/articles/Test+Data/61572/

    The method would allow you to concatenate all the stakeholders into a single column.

    If you want a tested answer, if you could provide a CREATE TABLE statement and some data according to the links in both BitBucket's and my signature line, I believe we could probably churn something out for you... 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 25, 2008 at 5:50 am

    #862467

    Thanks guys but this is not what i was looking for. I know how cross tabs and pivots work.

    I actually managed to solve the problem using SQL Server Reporting Services

    The way to do it is using Reporting Services groups and layer groups over each other.

    Thanks anyways

  • August 25, 2008 at 10:15 pm

    #862894

    Agh! My bad... I didn't read the original post as well as I should have. Throwing in a partitioned row number along with the a method similar to the cross tab method works just fine... without reporting services if you don't need it. Sorry for the confusion...

    --===== Create and populate a test table.

    -- This is NOT a part of the solution

    DECLARE @DemoTable TABLE

    (ProjectName VARCHAR(10), Field1 VARCHAR(10), Field2 VARCHAR(10), StakeHolder VARCHAR(15))

    INSERT INTO @DemoTable

    (ProjectName, Field1, Field2, StakeHolder)

    SELECT 'projectA','xxxx','yyyy','stakeholderA' UNION ALL

    SELECT 'projectA','xxxx','yyyy','stakeholderB' UNION ALL

    SELECT 'projectA','xxxx','yyyy','stakeholderC' UNION ALL

    SELECT 'projectB','xxyy','xxyy','stakeholderB' UNION ALL

    SELECT 'projectC','aaaa','bbbb','stakeholderB' UNION ALL

    SELECT 'projectC','aaaa','bbbb','stakeholderC' UNION ALL

    SELECT 'projectD','cc','dd','stakeholderA' UNION ALL

    SELECT 'projectD','cc','dd','stakeholderB' UNION ALL

    SELECT 'projectD','cc','dd','stakeholderD' UNION ALL

    SELECT 'projectD','cc','dd','stakeholderJ' UNION ALL

    SELECT 'projectD','cc','dd','stakeholderK' UNION ALL

    SELECT 'projectD','cc','dd','stakeholderL' UNION ALL

    SELECT 'projectD','cc','dd','stakeholderM' UNION ALL

    SELECT 'projectD','cc','dd','stakeholderN' UNION ALL

    SELECT 'projectD','cc','dd','stakeholderO' UNION ALL

    SELECT 'projectD','cc','dd','stakeholderP' UNION ALL

    SELECT 'projectD','cc','dd','stakeholderC' UNION ALL

    SELECT 'projectD','cc','dd','stakeholderZ'

    ;WITH

    cteCreateRank AS

    (--==== Cte returns data with ordinal ranked rows in sorted order

    SELECT ROW_NUMBER() OVER (PARTITION BY ProjectName, Field1, Field2

    ORDER BY ProjectName, Field1, Field2, StakeHolder) AS RowNum,

    ProJectName, Field1, Field2, StakeHolder

    FROM @DemoTable

    )--==== This decides when to print the first 3 columns of data

    SELECT CASE WHEN RowNum = 1 THEN ProjectName ELSE '' END AS ProjectName,

    CASE WHEN RowNum = 1 THEN Field1 ELSE '' END AS Field1,

    CASE WHEN RowNum = 1 THEN Field2 ELSE '' END AS Field2,

    StakeHolder

    FROM cteCreateRank

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply