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!
SQL Server 2017 has this lovely function called
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
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 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…