At some point you might need to turn multiple rows from a column into a delimited list. I think I saw this done for the first time about 3 years ago, and I clearly remember having to do a double take at the code upon seeing it. It’s been in my sql-toolbox since then, though admittedly it doesn’t see much action. At any rate I thought I’d share, so let’s set up a scenario to walk through. Let’s say we have a table called ‘Numbers’ that has a column named ‘ID’ that contains integers. If we ran the following query:
SELECT * FROM Numbers
We would get the following results:
The table basically contains the values 1-6 with the value 5 appearing twice. What we want to achieve is to get our values to appear as one delimited (comma or otherwise) string like so:
‘1, 2, 3, 4, 5, 6’
We can use the COALESCE function in combination with a variable to achieve the desired results using the code below, just make sure to change the table and column name to suit your specific scenario.
--declare local variables
DECLARE
@v_DelimitedString VARCHAR(MAX)
--pivot rows into delimited string
SELECT
@v_DelimitedString = COALESCE(@v_DelimitedString, '') + CONVERT(VARCHAR(10), ID) + ','
FROM
Numbers
--trim off last comma
SET @v_DelimitedString = (SELECT SUBSTRING(@v_DelimitedString, 1, LEN(@v_DelimitedString)-1))
--show results
SELECT @v_DelimitedString
Running this code against our Numbers table would produce the following results.
That’s almost what we’re looking for, although we’re seeing the value ‘5’ repeated multiple times since it appears multiple times in our data. This problem is easy enough to solve though, by replacing our ‘Numbers’ table with a dynamic SELECT statement like so (see green highlight for changed code below)
--declare local variables
DECLARE
@v_DelimitedString VARCHAR(MAX)
--pivot rows into delimited string
SELECT
@v_DelimitedString = COALESCE(@v_DelimitedString, '') + CONVERT(VARCHAR(10), ID) + ','
FROM
(SELECT DISTINCT ID FROM Numbers) Nbrs
--trim off last comma
SET @v_DelimitedString = (SELECT SUBSTRING(@v_DelimitedString, 1, LEN(@v_DelimitedString)-1))
--show results
SELECT @v_DelimitedString
Now our repeating 5 value has been eliminated and our results are being returned as so:
It’s a pretty nifty trick that was a little more useful before table value parameters were available. It can still come in handy if you wanted to do something like use the T-SQL PIVOT function to return a dynamic set of columns. I’ll be covering how to do that in my next post. Hope this helps.
Subscribe