What you want to do is read up on
Concatenation
Concatenating Row Values in Transact-SQL - Simple Talk[
^]
This example is similar to the XML tricks, sorry I didn't play too much with it to work out the INT vs VARCHAR
declare @table table (
ID varchar(16)
, ParentID varchar(16)
, [Name] varchar(16)
)
insert @table
values (1, 0, 'FLOWERS'), (2, 1, 'rose'), (3, 1, 'lily')
, (4, 1, 'lotus'), (5, 0, 'ANIMALS'), (6, 5, 'dog'), (7, 5, 'cat')
, (8, 5, 'rat'), (9, 5, 'monkey'), (10, 0, 'GAMES'), (11, 10, 'cricket')
, (12, 10, 'football'), (13, 10, 'basketball'), (14, 10, 'badminton')
SELECT p.[Name]
, Children = SUBSTRING( (
SELECT ','+c.ID AS [text()]
FROM @table c
WHERE c.ParentID = p.ID
ORDER BY c.ID
FOR XML PATH ('')
), 2, 1000)
FROM @table p
WHERE p.ParentID = 0
order by p.ID
But it does give the results you want
Name Children
-------- ---------
FLOWERS 2,3,4
GAMES 11,12,13,14
ANIMALS 6,7,8,9