SQL: Create and split comma separated values

A common task in SQL server is to create a comma separated list with the values found in a column. For this you can use:

SELECT STUFF( (SELECT ', '+ [MyColumn] FROM [MyTable] FOR XML PATH('')),1,2,'')

To retrieve the values from a comma separated list use:

DECLARE @xml AS XML
SET @xml = '<id>' + REPLACE(@CommaSeparatedValues,',', '</id><id>')  + '</id>'
SELECT Columns.id.value('.','int') AS [id] FROM @xml.nodes('id') Columns(id)

Below is a working example:

----------------------------------------------------------------
--Demo table
----------------------------------------------------------------
DECLARE @Tmp TABLE ([Key] int, [Value] nvarchar(50))

INSERT INTO @Tmp ([Key], [Value]) VALUES (1, 'Red')
INSERT INTO @Tmp ([Key], [Value]) VALUES (2, 'Orange')
INSERT INTO @Tmp ([Key], [Value]) VALUES (3, 'Yellow')
INSERT INTO @Tmp ([Key], [Value]) VALUES (4, 'Green')
INSERT INTO @Tmp ([Key], [Value]) VALUES (5, 'Blue')
INSERT INTO @Tmp ([Key], [Value]) VALUES (6, 'Indigo')
INSERT INTO @Tmp ([Key], [Value]) VALUES (7, 'Violet')

DECLARE @CommaSeparatedValues nvarchar(max)

----------------------------------------------------------------
--Get comma separated values
----------------------------------------------------------------
SET @CommaSeparatedValues =  STUFF( (SELECT ', '+ [Value]
				     FROM @Tmp 
			             FOR XML PATH('')),1,2,'')          
SELECT @CommaSeparatedValues

----------------------------------------------------------------
--Split comma separated values
----------------------------------------------------------------
SET @CommaSeparatedValues = '1,3,5,7'

DECLARE @xml AS XML
SET @xml = '' + REPLACE(ISNULL(@CommaSeparatedValues,''), ',', '')  + ''

SELECT [Key],[Value]
FROM @Tmp
WHERE [Key] IN (SELECT Columns.id.value('.','int') AS [id]
                FROM @xml.nodes('id') Columns(id))
Advertisements

One comment on “SQL: Create and split comma separated values

  1. A trick I used to use in creating a comma separated list from SQL values was using a variable like this:
    DECLARE @csv NVARCHAR(Max) = N”
    SELECT @csv=@csv+’, ‘+ColumnValue FROM Table

    Options with string manipulations exist as well for reading a CSV from SQL, but that was only because I hated any use of XML šŸ™‚ Your solutions seem much more reasonable.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s