Adding the list of children to the parent row in SQL Server

Question

I have the following issue with my query. I have this table:

Table1:

ID     Name     Child
----------------------
130     a         150  
130     a         225
130     a         565
130     a         343
130     a        2225

All I am trying is using group to output to query to display one record which is 130 but show all the children as a new column as a comma separated list inside that parent row.

How can I do this?

I am using front end to do this work but that is setting the data in all rows

I am looking for a result like this:

ID     Name       Children
-----------------------------------------
130     a         150,225,565,343,2225

I am using SQL Server

Possible duplicate of [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — Alex Kudryashev, Jun 19 '18 at 20:21

score 4 · Accepted Answer · answered Jun 19 '18 at 20:12

4

SELECT 
   ID,
   STUFF((SELECT ', ' + v2.Child 
          FROM Table1 v2
          WHERE v2.ID = v1.ID
          ORDER BY Child
          FOR XML PATH('')), 1, 1, '') [Child]
FROM Table1 v1
GROUP BY v1.ID, v1.Name
ORDER BY 1

I think this will do it for you, if not maybe a close guess without data. But! I used STUFF and selected the Child and split them by , and ordered it

answered Jun 19 '18 at 20:12

t..

1,101
1
9
22

i cannot use `group` by in my sql server, because that is used in my `cfoutput` group. can i use it without group – userjquery Jun 20 '18 at 12:13
there is no column as `Children`, it needs to be created before it can be used – userjquery Jun 20 '18 at 12:18
getting an error on this section: `STUFF((SELECT CONVERT(int,CONVERT(varchar, ', ' + v2.child))` - `Msg 245, Level 16, State 1, Line 56 Conversion failed when converting the varchar value ', ' to data type int. ` – userjquery Jun 20 '18 at 12:36

score 3 · Answer 2 · answered Jun 19 '18 at 20:23

You probably should be looking at the function STRING_AGG for this purpose. The query will be something like this:

select id, name, STRING_AGG(Child, ',') as Children
from Table1
group by id, name;

Note: There may be something to modify since I was not able to test it as I didn't find online site for sql server.

Adding the list of children to the parent row in SQL Server

2 Answers2