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:
[code language=”SQL”]
USE tempdb;
— Test table
SELECT dt.comments
FROM ( VALUES ( ‘XML is the bomb!’),
( ‘& JSON is cool too…’)
) AS dt (comments);
[/code]

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…
[code language=”SQL”]
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;
[/code]

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.
[code language=”SQL”]
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;
[/code]

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(”)”