How to Pivot multiple rows of data into a comma delimited list/string using COALESCE

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:

clip_image001[4]

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.

clip_image002[4]

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:

clip_image003[4]

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.