SQL - Make a comma delimited column from other columns

Question

I am attempting to make a column in which it is a comma delimited list of other columns where the id is the same. How can I take the query I currently have, and update it to get the output that I expect?

T1

PK
1
2

T2

ID	Name	T1.PK
1	Apple	1
2	Pear	1
3	Orange	1
4	Cat	2
5	Dog	2

My hope is I can join these tables and get the following result.

PK	Name
1	Apple, Pear, Orange
2	Cat, Dog

I have a rough idea of how to accomplish this, but I do not see how to separate the list, instead I will get Apple, Pear, Orange, Cat, Dog for each row instead of them being separated. Here is the query I am currently working with.

select t1.PK,
stuff(
   (select distinct ', ' + Name
    from t2
    for XML PATH ("")), 1, 2, '') Name
from t1
left join t2

Not sure I'm understanding the question, but do you have a `GROUP BY t1.PK` in your query? Please post the full query (including the `ON` and `GROUP BY`). — EdmCoff, Jun 02 '22 at 20:37
Recursive CTE, Connect By Prior, String_Agg, List_Agg, Stuff, For XML Path... just depends on RDBMS/Version and availability of these features. — xQbert, Jun 02 '22 at 21:13

score 2 · Answer 1 · answered Jun 02 '22 at 20:48

2

In new SQL Server DB's (since SQL Server 2017), it's also possible to use STRING_AGG for this purpose:

SELECT t1.pk, STRING_AGG (t2.name,', ') AS name
FROM t1 JOIN t2 ON t1.pk = t2.[t1.pk]
GROUP BY t1.pk;

answered Jun 02 '22 at 20:48

Jonas Metzler

4,517
1
5
17

Unfortunately I cannot use string_agg. Thank you. – Penx Jun 03 '22 at 12:33

score 1 · Accepted Answer · answered Jun 02 '22 at 20:42

1

select t1.PK,
stuff(
   (select ', ' + Name
    from t2
    where t2.[t1.PK] = t1.PK 
    for XML PATH ('')), 1, 1, '') Name
from t1

I answered this question about 12 years ago

https://stackoverflow.com/a/1785923/215752

answered Jun 02 '22 at 20:42

Hogan

69,564
10
76
117

SQL - Make a comma delimited column from other columns

2 Answers2