Flatten hierarchy

  • I know there have been several forum discussions about flattening hierarchies using recursive CTEs but the examples I have found have a ParentID field in the data. My data does not have this.

    Please take a look at this sample code

    declare @table table

    (id varchar(10),

    levelid int,

    leveldesc varchar(250))

    insert into @table

    select 'DP961A1111', 1, 'LevelDesc1'


    select 'DP961A1111', 2, 'LevelDesc2'


    select 'DP961A1111', 3, 'LevelDesc3'

    select * from @table

    --I need to display the results like this:

    select 'DP961A1111' as id, 'LevelDesc1' as LevelDesc1, 'LevelDesc2' as LevelDesc2, 'LevelDesc3' as LevelDesc3

    Can you guys help?

  • Try this - it's called a CROSSTAB query:

    SELECT id,

    LevelDesc1 = MAX(CASE WHEN levelid = 1 THEN leveldesc ELSE NULL END),

    LevelDesc2 = MAX(CASE WHEN levelid = 2 THEN leveldesc ELSE NULL END),

    LevelDesc3 = MAX(CASE WHEN levelid = 3 THEN leveldesc ELSE NULL END)

    FROM @table

    GROUP BY id

    Here's a great article to explain how it works, and how far you can go with it:


    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I have came across the crosstab query before but only in theory, never used it in anger! I'll give this a go.

    Thanks for your help, greatly appreciated.

Viewing 3 posts - 1 through 2 (of 2 total)

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