3

If I have a table with the following information:

ParentID | ChildID
---------+--------
     6   |      6 
     6   |      7 
     6   |      8 
     6   |      9 
     6   |     10 

I need to display the distinct parent with all instances of child in the second column possibly concatonated with a comma e.g.

ParentID | ChildID
---------+--------
   6     |     6, 7, 8, 9, 10

Thanks in advance!

0

2 Answers 2

4

You can use a pattern like this:

SELECT files.database_id, db.name AS DatabaseName,
        STUFF((SELECT ', ' + names.name
                FROM sys.master_files names
                WHERE names.database_id = files.database_id
                FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
            , 1, 2, '') AS NameList
FROM sys.master_files files
JOIN sys.databases db
    ON files.database_id = db.database_id
GROUP BY files.database_id, db.name
0

You could try :

select  distinct parentId,
        substring(( select ','+cast(childId  as varchar)
                    from MyTable t1 with(nolock)
                    where t1.parentId = t2.parentId             
                    for xml path ('')
                ),2, 1000) as childId
from MyTable t2
1
  • 1
    You should switch from substring to stuff. The way you have it currently if your string is longer than 1000 characters you will lose the end of it. Stuff allows you to remove the first X number of characters and still retain the rest of it. Commented May 23, 2016 at 15:08

Not the answer you're looking for? Browse other questions tagged or ask your own question.