CODEWITHSUNDEEP
t-sqlcommon-table-expressionhierarchical-data
phoenix
phoenix

Reputation: 71

How to display all parents IDs in one line (row) from hierarchical table

I have a hierarchical table where for each child there is defined a parent id (except of top level child where parent id is null). Now I would like to display each parent id in one line for selected child.

I was trying to use CTE but CTE is adding additional records to initial set. Instead of that I would like to update the initial set (SELECT statement selecting some children) with list of parents.

Example of table (Table1):

ChildID   ParentID
A         P
P         Q
Q         Z
B         P
C         F
F         M
M         Q
D         H
H         Y
Y         NULL
Z         NULL

If initial statement will select C, A, Q from list of children IDs then expected result is following:

Selected Child    Parent IDs
C                 F, M, Q, Z
A                 P, Q, Z
Q                 Z

Upvotes: 0

Views: 2361

Answers (1)

bobthedeveloper
bobthedeveloper

Reputation: 3783

You were right about doing this with a CTE, but then you have to take all the rows and put them into one column (see cross apply - xml path).

with cte (selected, child, parent) as 
(  
  select s.id, t.*
  from @t as t
  inner join @selected as s on t.childid = s.id

  union all

  select cte.selected, t.*
  from @t as t
  inner join cte on t.childid = cte.parent
  where cte.parent is not null
)

select distinct
  t.selected as [Selected Child], 
  left(a.parents,len(a.parents) - 1) as Parents
from  cte t
  cross apply (select  cast(parent + ',' as text)
           from cte tt
           where t.selected = tt.selected
           for xml path('')) a(parents);

With sample data:

declare @t as table
( 
  childid char(1),
  parentid char(1)
);

declare @selected as table
(
  id char(1)
);


insert into @t (childid,parentid) values ('a','p'),
           ('p','q'),
           ('q','z'),
           ('b','p'),
           ('c','f'),
           ('f','m'),
           ('m','q'),
           ('d','h'),
           ('h','y'),
           ('y',null),
           ('z',null);

insert into @selected (id) values ('c'),('a'),('q');

with cte (selected, child, parent) as 
(  
  select s.id, t.*
  from @t as t
  inner join @selected as s on t.childid = s.id

  union all

  select cte.selected, t.*
  from @t as t
  inner join cte on t.childid = cte.parent
  where cte.parent is not null
)

select distinct
  t.selected as [Selected Child], 
  left(a.parents,len(a.parents) - 1) as Parents
from  cte t
  cross apply (select  cast(parent + ',' as text)
           from cte tt
           where t.selected = tt.selected
           for xml path('')) a(parents);

Upvotes: 6

Related Questions