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

Question

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!

Answer 1

Try this:

  Select C.CatId, C.Name, PC.Name, GP.Name, GGP.Name
  From Category C
    Left Join Category PC On PC.CatId = C.ParentCategoryId
    Left Join Category GP On GP .CatId = PC.ParentCategoryId
    Left Join Category GGP On GGP .CatId = GP.ParentCategoryId

Based on yr comment, if you write a UDF as follows:

Create Function CatParentNames
( @CatId Integer )
Returns varchar(1000)
AS
Begin
     Declare @outVal VarChar(1000)
     Declare @ParId Integer
     Select @ParId = ParentCategoryId, @outVal = Name
     From Category 
     Where CatId = @CatId
     While Exists(Select * From Category 
                  Where CatId = @ParId)
        Begin
           Select @ParId = ParentCategoryId, 
                 @outVal = Name + ', ' + @outVal
           From Category 
           Where CatId = @ParId
        End

 Return @outVal

End

then, write your sql as follows:

Select CatId, dbo.CatParentNames(CatId)
From Category
Where ParentCategoryId Is Not Null

Answer 2

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)