Creating Comma-Delimited Lists in SQL

July 17th, 2006

Today at work I came across a situation where I need to create a comma-delimited list out of a relational database table. In other words, I have a table that looks like this:

Style | Color
1 | Blue
1 | Black
1 | White
2 | Blue
2 | Black
2 | Grey
3 | Red
3 | Blue
3 | Black
4 | Red
4 | White
4 | Blue

I need to return a list of all unique styles with a comma delimited list of colors, like this:

Style | Color List
1 | Blue, Black, White
2 | Blue, Black, Grey
3 | Red, Blue, Black
4 | Red, White, Blue

I looked around and the simplest way seems to be creating a user-defined function that employs the COALESCE function. It's a very clean solution. The full text of the function is at 4guysfromrolla.com.

CREATE FUNCTION dbo.List_Function(@Style INT)
RETURNS VARCHAR(200) AS

BEGIN

DECLARE @List varchar(200)

SELECT @List = COALESCE(@List + ', ', '') + a.Color
FROM Color_Table a
WHERE a.Style = @Style

RETURN @List

END

When I create the function I can use the following SQL statement to call up my table:

SELECT
Style, List_Function(Style) as Color_List
FROM
Color_Table
GROUP BY
Style