Hey SQL gurus! I’m scratching my head over this one.
I’ve got a table with an Id column and a Name column. It looks like this:
Id
Name
1
aaa
1
bbb
1
ccc
1
ddd
1
eee
I’m trying to get all the names for each Id combined into one column, separated by commas. So the output should be:
Id
abc
1
aaa,bbb,ccc,ddd,eee
I found this query that does the job:
SELECT ID,
abc = STUFF(
(SELECT ',' + name FROM myTable FOR XML PATH ('')), 1, 1, ''
)
FROM myTable GROUP BY id
It works but I’m totally lost on how STUFF and FOR XML PATH are doing their magic here. Can someone break it down for me? Or is there an easier way to do this? Thanks!
huh, that’s a neat trick! i’m curious tho - does this method work for all datatypes? like, could you use it to concatenate numbers or dates too? and how does it handle null values? maybe there’s a way to customize the separator? anyone tried other approaches?
The STUFF and FOR XML PATH combination is a clever workaround for string concatenation in SQL Server. Here’s what happens under the hood:
FOR XML PATH(‘’) converts each row into an XML fragment, effectively creating a concatenated string from multiple rows. Since an empty string is provided as the XML element name, no XML tags are generated around the values.
The subquery SELECT ‘,’ + name concatenates a comma in front of every name, resulting in a string like “,aaa,bbb,ccc”.
STUFF then removes the leading comma by deleting the first character (position 1, one character) and replacing it with an empty string.
In SQL Server 2017 and later, you can use STRING_AGG to achieve the same result with a more straightforward and intuitive syntax.
yo, that STUFF and FOR XML PATH thing’s pretty slick! but ngl, it looks kinda messy. have u tried using STRING_AGG instead? its way simpler and does the same job. just do STRING_AGG(name, ‘,’) WITHIN GROUP (ORDER BY name) and ur good to go. works like a charm!
anyone tried other approaches?