What is the fastest / easiest way to denormalize this hierarchical 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 that in this example, the data does not include a sheet on an earlier node than level 4, but this is possible). This data will never be deeper than level 4, if that matters. I would like to convert / rotate 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 made a left outer join to the category table 4 times and built a huge case statement to determine the level that will be used for the ID field, but which does not include zero lines .... Any ideas? HELP!

Answer 1

this is probably not the most efficient query, but it is 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) 

Answer 2

Try the following:

  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 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 3

Try it:

 select a.CategoryId as CatId, a.CategoryName as Name1, cast(null as varchar(20)) as Name2, cast(null as varchar(20)) as Name3, cast(null as varchar(20)) as Name4 from @YourTable a where a.ParentCategoryId is null union all select b.CategoryId, a.CategoryName, b.CategoryName, null, null from @YourTable a inner join @YourTable b on a.CategoryId = b.ParentCategoryId where a.ParentCategoryId is null union all select c.CategoryId, a.CategoryName, b.CategoryName, c.CategoryName, null from @YourTable a inner join @YourTable b on a.CategoryId = b.ParentCategoryId inner join @YourTable c on b.CategoryId = c.ParentCategoryId where a.ParentCategoryId is null union all select d.CategoryId, a.CategoryName, b.CategoryName, c.CategoryName, d.CategoryName from @YourTable a inner join @YourTable b on a.CategoryId = b.ParentCategoryId inner join @YourTable c on b.CategoryId = c.ParentCategoryId inner join @YourTable d on c.CategoryId = d.ParentCategoryId order by 2, 3, 4, 5 

But this is not the way to build it if you are going to use it with a multi-column TreeView that looks like this:

_{(source: digitaltools.com )}

More can be found here.