How to convert a command delimited list to single values with MSSQL

You often need to convert comma delimited list (csv) such as:
000010,000030,000110,000190,000310 To one value per row:

  1. 000010
  2. 000030
  3. 000110
  4. 000190
  5. 000310

This is the opposite of of the pivot, it's an "unpivot".

To see how to create a comma delimited list (csv) from a table see: How to retrieve a comma delimited list (csv) of unique values with MSSQL

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Data used

  1. USE sql911;
  2. SELECT sales_date,sales_qty,emp_short
  3. FROM sales;
  4. go
[Get Code]
sales_date              sales_qty                               emp_short
----------------------- --------------------------------------- ----------
2005-10-06 10:01:01.000 10.00                                   000310
2006-10-06 10:01:01.000 10.00                                   000310
2005-11-06 10:01:01.000 0.00                                    000110
2006-10-06 10:01:01.000 10.00                                   000310
2005-12-06 11:01:01.000 8.00                                    000010
2006-02-06 13:01:01.000 NULL                                    000190
2006-03-06 14:01:01.000 5.00                                    000110
2006-10-06 10:01:01.000 10.00                                   000190
2006-10-06 10:01:01.000 10.00                                   000190
2006-04-06 15:01:01.000 4.00                                    000030
2006-05-06 16:01:01.000 3.00                                    000010
2004-10-06 10:01:01.000 NULL                                    000310
2005-11-06 10:01:01.000 9.00                                    000010
2006-06-06 17:01:01.000 0.00                                    000190
2006-01-06 12:01:01.000 7.00                                    000310
2006-02-06 13:01:01.000 6.00                                    000110
2005-03-06 14:01:01.000 5.00                                    000190
2006-10-06 10:01:01.000 10.00                                   000110
2006-10-06 10:01:01.000 3.00                                    000110
2007-06-06 17:01:01.000 2.00                                    000010
2007-04-06 00:00:00.000 0.00                                    000010
2007-05-01 00:00:00.000 5.00                                    000030
2007-07-06 00:00:00.000 1.00                                    000190
2007-05-03 09:00:00.000 3.00                                    000010
2007-05-05 04:00:00.000 1.00                                    000010
2006-10-06 10:01:01.000 10.00                                   000030
2004-03-06 14:01:01.000 NULL                                    000110
2005-04-06 15:01:01.000 4.00                                    000010
2005-05-06 16:01:01.000 3.00                                    000190
2006-06-06 17:01:01.000 2.00                                    000030
2006-07-06 19:01:01.000 1.00                                    000310
2006-03-06 14:01:01.000 5.00                                    000310
2006-04-06 15:01:01.000 4.00                                    000010
2007-05-07 11:00:00.000 2.00                                    000010
2006-06-06 17:01:01.000 NULL                                    000110
2006-07-06 19:01:01.000 1.00                                    000190
2005-10-06 10:01:01.000 10.00                                   000030
2007-05-08 15:00:00.000 1.50                                    000010
2005-12-06 11:01:01.000 8.00                                    000190
2006-01-06 12:01:01.000 7.00                                    000030
2007-05-07 11:30:00.000 2.00                                    000030
2007-05-08 13:30:00.000 1.00                                    000030
2006-07-06 19:01:01.000 1.00                                    000010
2007-05-09 14:00:00.000 1.25                                    000110
2007-11-06 10:01:01.000 9.00                                    000030
2007-05-10 14:00:00.000 5.00                                    000190
2007-05-11 14:15:00.000 0.00                                    000190
2007-05-12 15:00:00.000 0.00                                    000190
2007-05-13 16:00:00.000 1.00                                    000190
2007-05-08 05:00:00.000 1.00                                    000310
2007-05-07 00:00:00.000 2.00                                    000310
2007-05-14 11:00:00.000 3.00                                    000310
2006-07-06 00:00:00.000 1.00                                    000190
2007-05-11 13:00:00.000 3.50                                    000310
2007-05-08 05:00:00.000 0.00                                    000010
2007-05-08 07:00:00.000 2.00                                    000010
2007-05-08 11:00:00.000 3.00                                    000010

(57 row(s) affected)

Create a comma delimited list (csv) of unique values

For detailed explanations on how to do it clear here: How to retrieve a comma delimited list (csv) of unique values with MSSQL. This is the equivalent of the pivot command.

  1. DECLARE @uniquelist VARCHAR(1000);
  2. SELECT  @uniquelist = LTRIM(COALESCE(@uniquelist+',' , ' ')) + CAST(emp_short AS VARCHAR(10))
  3. FROM (SELECT DISTINCT emp_short FROM sales) ABC;
  4. SELECT @uniquelist AS 'Unique emp_short list: comma delimited';
[Get Code]
Unique emp_short list: comma delimited
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
000010,000030,000110,000190,000310

(1 row(s) affected)

Convert the comma delimited list (csv) to a single value list

The goal is to convert: 

000010,000030,000110,000190,000310

to: 

000010
000030
000110
000190
000310
  1. DECLARE @uniquelist VARCHAR(1000);
  2. SELECT  @uniquelist = LTRIM(COALESCE(@uniquelist+',' , ' ')) + CAST(emp_short AS VARCHAR(10))
  3. FROM (SELECT DISTINCT emp_short FROM sales) ABC;
  4. SELECT @uniquelist AS 'Unique emp_short list: comma delimited';
  5. DECLARE @currentvalue VARCHAR(10)
  6. SET @uniquelist = @uniquelist+','
  7. PRINT 'Single values again';
  8. PRINT '-------------------';
  9. WHILE CHARINDEX(',',@uniquelist) > 0
  10.  BEGIN
  11.     SET @currentvalue = SUBSTRING(@uniquelist,1,CHARINDEX(',',@uniquelist)-1);
  12.     PRINT @currentvalue;
  13.     SET @uniquelist = SUBSTRING(@uniquelist,CHARINDEX(',',@uniquelist)+1,datalength(@uniquelist));
  14.  END
  15. go
[Get Code]
Unique emp_short list: comma delimited
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
000010,000030,000110,000190,000310

(1 row(s) affected)

Single values again
-------------------
000010
000030
000110
000190
000310
  1. I had to redo the creation of the comma delimited list (csv) on line 1 to 4, because the variable @uniquelist only exist in the current transaction.
  2. Line 6 adds a comma at the end for simplification purpose.
  3. Line 11, gets the current value.
  4. The print, on line 12, can be replaced by any other valid SQL statement, such as insert, delete, or update based on that value or with other queries.
  5. Line 13 gets rid of the current value and the while charindex(',',@uniquelist) > 0, on line 9, keeps going until there is no more comma left and we have eliminated all the values.