Working on a blog post and I came up against a problem that I had heard of before but did not spend much brain-CPU power against.
I know I’m going to run into this again so let’s document this for future me. Oh he’s going to appreciate this so much!
Commas are all the rage nowadays:
There are a fair number of questions nowadays about returning data from a database in a comma separated string. Sure the application should probably do that but hey, database servers are expensive, why not get some bang for your bucks!
Done!-ish…
SQL Server 2017 has this lovely function called STRING_AGG()
Pop in your column and your separator and it takes care of it for you!
…wait not everyone has SQL Server 2017 yet?
…wait I don’t have SQL Server 2017 yet? Oh, I should really fix that…
Pre-SQL Server 2017:
So what can we do if we are not on SQL Server 2017? Take the advice that I was given for most of my life and STUFF
it!
The STUFFing:
Our playground:
USE tempdb; -- Test table SELECT dt.comments FROM ( VALUES ( 'XML is the bomb!'), ( '& JSON is cool too...') ) AS dt (comments);

Plain Old STUFFing:
I’m not the biggest fan of stuffing if I’m honest…tastes like dirt to me but hey, it works in 99% of situations…
SELECT STUFF((SELECT ', ' + dt.comments FROM ( VALUES ( 'XML is the bomb!'), ( '& JSON is cool too...') ) AS dt (comments) FOR XML PATH('') ), 1, 1, '') AS CommentsEnXML;

Bacon Sausage STUFFing however:
So…SQL Server is trying to be too helpful. What do we do? I normally turn to the SQL community and people like Rob Farley ( blog | twitter ), who has a lovely post about this.
So let’s try it out.
SELECT STUFF((SELECT ', ' + dt.comments FROM ( VALUES ( 'XML is the bomb!'), ( '& JSON is cool too...') ) AS dt (comments) FOR XML PATH(''), TYPE).value('.', 'varchar(max)' ), 1, 1, '') AS CommentsEnXML;

I just eat the turkey around the STUFFing:
Little hiccup in preparing for my next post. Thankfully I learn from my mistakes and failures (there’d be no help for me otherwise!).
I’ll leave this post with a quote from the blog of the main man himself:
It’s a habit I need to use more often.
Yeah, me too Rob, me too…
One thought on “Why You May Need More Than FOR XML PATH(”)”