How to retrieve a comma delimited list of unique values with MSSQL
You often need to export columns as comma delimited values, basically a pivot function, but for export to be properly understood by other applications such as Microsoft Office Excel,...
It's better to do the pivot functions on the client, but it's often not available, the receiving application may not be able to pivot the data.
Data used
- USE sql911;
- SELECT sales_date,sales_qty,emp_short
- FROM sales;
- go
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)
Comma delimited list
We will be using the emp_short column for creating a comma delimited list.
- DECLARE @biglist VARCHAR(1000);
- SELECT @biglist = COALESCE(@biglist+',' , ' ') + CAST(emp_short AS VARCHAR(10))
- FROM sales;
- SELECT @biglist AS 'All emp_short list comma delimited';
- go
All emp_short list comma delimited ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 000310,000310,000110,000310,000010,000190,000110,000190,000190,000030,000010,000310,000010,000190,000310,000110,000190,000110,000110,000010,000010,000030,000190,000010,000010,000030,000110,000010,000190,000030,000310,000310,000010,000010,000110,000190,000 (1 row(s) affected)
- The select only display the first 255 characters of the variable @biglist.
- The coalesce(@biglist+',' , ' ') is what creates the comma delimited list.
- The cast(emp_short AS varchar(10)) ensures that there is no space between each value.
- The comma delimited list has every instance, 1 value per row.
- You need to declare the biglist large enough to hold all the values.
- Whenever you use buglist, you will need the @ to use the declared variable.
Comma delimited list with unique values
We want only one occurrence for each emp_short, not multiple values of the same emp_short.
- DECLARE @uniquelist VARCHAR(1000);
- SELECT @uniquelist = COALESCE(@uniquelist+',' , ' ') + CAST(emp_short AS VARCHAR(10))
- FROM (SELECT DISTINCT emp_short FROM sales) ABC;
- SELECT @uniquelist AS 'Unique emp_short list comma delimited';
- go
Unique emp_short list comma delimited ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 000010,000030,000110,000190,000310 (1 row(s) affected)
- Instead of using the sales table, you use a subquery that does the job of eliminated the duplicates with (SELECT DISTINCT emp_short FROM sales) ABC