I seem to remember posting on this subject back in December – but I can’t find it. Sigh. (note – just found the post, here – it’s down a ways but still there.) I have a table called Symptom that contains a hierarchical structure – i.e. a ID field and a ParentID field, in this implementation. (Yes I could have used HierarchyID – we opted not to, after looking at all our options.)

Note we could have used recursion. If this was >10 levels or so, I would do that. But SQL is great at joins and they execute very fast; we’ll use this approach for now. See http://www.codeproject.com/Articles/16032/Expanding-SQL-Hierarchies-The-Dualistic-Approach or the Microsoft SQL Server Bible 2008 by Paul Nielsen for more on expanding hierarchies and the adjacency pattern.

-- Retrieve the whole hierarchy from level 1 to level 5
declare @t table (
l1 int, l2 int, l3 int,  l4 int, l5 int, id int, depth int)

-- Populate @t: expand msg hierarchy for levels 1-5
insert into @t
select m1.SymptomID, m2.SymptomID, m3.SymptomID, m4.SymptomID, m5.SymptomID, null, 1
from Symptom as m1
left outer join Symptom m2 on m1.SymptomID=m2.ParentID
left outer join Symptom m3 on m2.SymptomID=m3.ParentID
left outer join Symptom m4 on m3.SymptomID=m4.ParentID
left outer join Symptom m5 on m4.SymptomID=m5.ParentID
where m1.ParentID is NULL

-- Calculate node level for each node and get tree depth
declare @depth int
update @t set depth = depth + 1 where l2 is not null
update @t set depth = depth + 1 where l3 is not null
update @t set depth = depth + 1 where l4 is not null
update @t set depth = depth + 1 where l5 is not null
select @depth = max(depth) from @t

-- Since we have made several joins, we have only leaf nodes of level 4 in  -- @t. Add missing leaf nodes of level 1
insert into @t select distinct l1, NULL, NULL, NULL, NULL, NULL, 1 from @t where l2 is not NULL
-- Add missing leaf nodes of level 2
insert into @t select distinct l1, l2, NULL, NULL, NULL, NULL, 2 from @t where l3 is not NULL
-- Add missing leaf nodes of level 3
insert into @t select distinct l1, l2, l3, NULL, NULL, NULL, 3 from @t where l4 is not NULL
-- Add missing leaf nodes of level 4
insert into @t select distinct l1, l2, l3, l4, NULL, NULL, 3 from @t where l5 is not NULL

-- Populate id field, get the rightmost msg id from @t
update @t set id=coalesce(l5, l4, l3, l2, l1)

select id
, depth
, SymptomFullDesc = 
CASE WHEN depth = 1 THEN l1.SymptomDesc 
WHEN depth = 2 THEN l1.SymptomDesc + ' > ' + l2.SymptomDesc
WHEN depth = 3 THEN l1.SymptomDesc + ' > ' + l2.SymptomDesc + ' > ' + l3.SymptomDesc 
WHEN depth = 4 THEN l1.SymptomDesc + ' > ' + l2.SymptomDesc + ' > ' + l3.SymptomDesc + ' > ' + l4.SymptomDesc
WHEN depth = 5 THEN l1.SymptomDesc + ' > ' + l2.SymptomDesc + ' > ' + l3.SymptomDesc + ' > ' + l4.SymptomDesc + ' > ' + l5.SymptomDesc
END
from @t t
left outer join Symptom l1 on t.l1 = l1.SymptomID
left outer join Symptom l2 on t.l2 = l2.SymptomID
left outer join Symptom l3 on t.l3 = l3.SymptomID
left outer join Symptom l4 on t.l4 = l4.SymptomID
left outer join Symptom l5 on t.l5 = l5.SymptomID
ORDER BY depth, SymptomFullDesc