How can I flatten a category hierarchy in SQL Server 2008?

Question

I have a table that contains a hierarchy of categories. It can have any number of category levels.

I need to display these categories in a simple string format, ie:

>>parent>>child1>>subchild>>...

This is the data sample:

Id  Parent  Name
1   NULL    Categories
4   NULL    Instrument
55  NULL    Genre
65  NULL    Geographical Place 
8   1   CLASSICAL
47  1   SOLO INSTRUMENTS
10694   1   STYLES
4521    4   Piano
1137    8   SOLO INSTRUMENTS
1140    8   WALTZES
841 47  PIANO
93328   55  CLASSICAL
93331   55  BLUES
93334   55  CLUB / ELECTRONICA
93339   55  CONTEMPORARY FOLK
93344   55  CHILDREN
94892   65  EUROPE
4180    10694   CLASSICAL - SOLO PIANO
94893   94892   Western & Southern  Europe
94900   94893   France

What application language? If C#/VB, do you have ability to use `LINQ`? If so, you can use something like this: http://stackoverflow.com/questions/3758162/rendering-a-hierarchy-using-linq — mellamokb, Nov 30 '11 at 00:40
Model Your Data Hierarchies With SQL Server 2008: http://msdn.microsoft.com/en-us/magazine/cc794278.aspx — Mitch Wheat, Nov 30 '11 at 00:42
Must be done from TSQL within a function, i wrote the cte that gathers the data in the table layout above i just can't seem to figure out how to get to show up in a linear form. — Vince, Nov 30 '11 at 00:43
Mitch Wheat - thank you, that was a great post. Getting me closer to a solution. — Vince, Nov 30 '11 at 01:00

score 2 · Accepted Answer · edited Jan 18 '21 at 12:30

Maybe someone has a better way to do the last part (getting rid of the intermediate strings), but this get's you there without hard coding any joins:

declare @Categories table
(
    categoryID int,
    categoryParentID int,
    categoryDesc nvarchar(100)
)
insert into @Categories values
(1,NULL,'Categories'),
(4,NULL,'Instrument'),
(55,NULL,'Genre'),
(65,NULL,'Geographical Place '),
(8,1,'CLASSICAL'),
(47,1,'SOLO INSTRUMENTS'),
(10694,1,'STYLES'),
(4521,4,'Piano'),
(1137,8,'SOLO INSTRUMENTS'),
(1140,8,'WALTZES'),
(841,47,'PIANO'),
(93328,55,'CLASSICAL'),
(93331,55,'BLUES'),
(93334,55,'CLUB / ELECTRONICA'),
(93339,55,  'CONTEMPORARY FOLK'),
(93344,55,'CHILDREN'),
(94892,65,'EUROPE'),
(4180,10694,'CLASSICAL - SOLO PIANO'),
(94893,94892,'Western & Southern  Europe'),
(94900,94893,'France')

;with CategoryCTE (categoryID, categoryString) as
(
    select categoryID, cast(categoryDesc as nvarchar(max)) as categoryString
    from @Categories 
    where categoryParentID is null
    
    union all
    
    select rsCat.categoryID, cast(CategoryCTE.categoryString + N'>>' + rsCat.categoryDesc as nvarchar(max))
    from @Categories rsCat
    inner join CategoryCTE on rsCat.categoryParentID = CategoryCTE.categoryID
)
select categoryString 
from CategoryCTE o
where not exists
(
    --eliminate intermediate strings
    select i.categoryString
    from CategoryCTE i
    where LEN(i.categoryString) != LEN(o.categoryString)
    and CHARINDEX(o.categoryString, i.categoryString) > 0
)

Working example.

Thank you, I'm going to give this a try as it looks more efficient than what I currently have. — Vince, Dec 07 '11 at 20:12
If MS added support for recursive functions, this would be so much easier. — Pål Thingbø, Mar 09 '14 at 20:13

Aaron · Answer 2 · 2011-11-30T02:14:15.313

Your data looks like it'll be 4 categories deep, so you could try something like this:

    SELECT parent.Name, child.Name, subchild.Name, subchild2.Name
    FROM categories parent
    LEFT JOIN categories child ON parent.Id = child.Parent
    LEFT JOIN categories subchild ON child.Id = subchild.Parent
    LEFT JOIN categories subchild2 ON subchild.Id = subchild2.Parent
    WHERE parent.Parent is NULL;

Of course, you'll need to adjust it if you end up having more than 4 levels of categories. This method will also end-up yielding NULL results for hierarchies that don't extend to all 4 levels.

How can I flatten a category hierarchy in SQL Server 2008?

2 Answers2