Hierarchical data - Recursive CTE plus PIVOT?

Question

I have the following self-referencing hierarchical table:

SubCodes

ID int PK
ParentID int
Code varchar(25)
FKID int 

e.g.

ID    FKID      ParentID    Code
1     25        NULL        1100
2     NULL      1           1110
3     NULL      2           1111

I would like to write a query such that I get back

FKID    Answer                IDOfLowestNode
25      '1100,1110,1111'      3

I suspect I need a CTE AND a PIVOT - but it's just twisting my brain as to how to do it - any clues?

Answer 1

Try this

declare @SubCodes table (ID int,ParentID int,Code varchar(25),FKID int)

insert into @SubCodes (ID,FKID,ParentID,Code)
values
(1,     25,        NULL,        1100),
(2,     NULL,      1,           1110),
(3,     NULL,      2,           1111)

;with cte as
(
  select ID RootID,ID,ParentID,Code, CAST(Code as varchar(500)) answer
  from @SubCodes
  where ParentID is null
  union all
  select cte.RootID,sc.ID,sc.ParentID,sc.Code, CAST(cte.answer+','+sc.Code as varchar(500))
  from @SubCodes sc
        join cte on cte.ID=sc.ParentID
)
select c.answer, r.id LowestNode
from cte c
     join (select RootID, MAX(id) id FROM cte GROUP BY RootID) r ON c.ID=r.id