SQLTeam.com | Weblogs | Forums

Parent child relation query


#1

HI team , i need to get all child of a parent in my query. here is my sample data and output.

Please suggest.

DECLARE @TBL TABLE
(ID INT IDENTITY,
ITEM VARCHAR(20) ,
PARENT VARCHAR(20) ,
NTYPE INT )

INSERT INTO @TBL (ITEM, PARENT,NTYPE)
VALUES('A','X','1') ,('B','X','1') ,('C','B','1') ,('D','B','1') ,('E','C','1')

i wanted to show data like below:

parent child
x a,b
b c,d
c e


#2
select PARENT
,Childs= SubString (( SELECT ', ' + T2.ITEM 
FROM @TBL as T2 
WHERE T1.PARENT = T2.PARENT 
FOR XML PATH ( '' ) ), 3, 1000) 
FROM @TBL as T1 
GROUP BY PARENT

See: Create A Comma Delimited List From a Column


#3

Careful now. Neither the posted code nor the code from the linked article with de-entitize some of the special characters (like ampersands, etc) that XML loses it's mind on. You need to include TYPE if any special of the XML-phobic characters can happen.