What is the fastest/easiest way to denormalize this heirarchical table into a flat table?

hierarchical-data, sql-server, sql-server-2005

Solution

this probably isn't the most efficient query, but it is the easiest to code:

declare @YourTable table (CategoryId int primary key, ParentCategoryId int , CategoryName varchar(50))

INSERT INTO @YourTable VALUES (1, null, 'SomeRoot')
INSERT INTO @YourTable VALUES (2, 1, 'SomeChild')
INSERT INTO @YourTable VALUES (3, 2, 'SomeGrandchild')
INSERT INTO @YourTable VALUES (4, 3, 'SomeGreatGrandchild')

INSERT INTO @YourTable VALUES (10, null, 'X_SomeRoot')
INSERT INTO @YourTable VALUES (20, 10, 'X_SomeChild')
INSERT INTO @YourTable VALUES (30, 20, 'X_SomeGrandchild')


Select
    c1.CategoryId, c1.CategoryName, c2.CategoryName, c3.CategoryName, c4.CategoryName
    From @YourTable          c1
        INNER JOIN @YourTable c2 On c1.CategoryId = c2.ParentCategoryId
        INNER JOIN @YourTable c3 On c2.CategoryId = c3.ParentCategoryId
        INNER JOIN @YourTable c4 On c3.CategoryId = c4.ParentCategoryId
    WHERE c1.ParentCategoryId IS NULL 
UNION
Select
    c1.CategoryId, c1.CategoryName, c2.CategoryName, c3.CategoryName, NULL
    From @YourTable          c1
        INNER JOIN @YourTable c2 On c1.CategoryId = c2.ParentCategoryId
        INNER JOIN @YourTable c3 On c2.CategoryId = c3.ParentCategoryId
    WHERE c1.ParentCategoryId IS NULL
UNION
Select
    c1.CategoryId, c1.CategoryName, c2.CategoryName, NULL, NULL
    From @YourTable          c1
        INNER JOIN @YourTable c2 On c1.CategoryId = c2.ParentCategoryId
    WHERE c1.ParentCategoryId IS NULL
UNION
Select
    c1.CategoryId, c1.CategoryName, NULL, NULL, NULL
    From @YourTable          c1
    WHERE c1.ParentCategoryId IS NULL
ORDER BY 2,3,4,5

OUTPUT:

SortB CategoryId  CategoryName CategoryName  CategoryName      CategoryName
----- ----------- ------------ ------------- ----------------- --------------------
1     1           SomeRoot     NULL          NULL              NULL
2     1           SomeRoot     SomeChild     NULL              NULL
3     1           SomeRoot     SomeChild     SomeGrandchild    NULL
4     1           SomeRoot     SomeChild     SomeGrandchild    SomeGreatGrandchild
1     10          X_SomeRoot   NULL          NULL              NULL
2     10          X_SomeRoot   X_SomeChild   NULL              NULL
3     10          X_SomeRoot   X_SomeChild   X_SomeGrandchild  NULL

(7 row(s) affected)

Problem

I have the following hierarchical table: ``` Table Category: CategoryId, ParentCategoryId, CategoryName 1, null, SomeRoot 2, 1, SomeChild 3, 2, SomeGrandchild 4, 3, SomeGreatGrandchild ``` (note this sample data doesn't include a leaf on an earlier node than level 4, but that is possible). The data will never go deeper than level 4, if that is relevant. I'd like to transform/pivot it to this fixed 4 level display ``` CatId, Name1, Name2, Name3, Name4 1, SomeRoot, null, null, null 2, SomeRoot, SomeChild, null, null 3, SomeRoot, SomeChild, SomeGrandchild, null 4, SomeRoot, SomeChild, SomeGrandchild, SomeGreatGrandchild ``` I've done left outer joining to the category table 4 times, and built a huge case statement for detecting the level to use for the ID field, but that doesn't include the null rows.... Any ideas? HELP!

Original source