Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Creating a comma-separated list (SQL Spackle)

By Wayne Sheffield, (first published: 2011/01/12)

"SQL Spackle" is a collection of short articles written based on multiple requests for similar code. These short articles are NOT meant to be complete solutions. Rather, they are meant to "fill in the cracks".

--Phil McCracken

Introduction

A frequent request found on the forums is to generate a comma-separated list of values, usually while maintaining other columns in a normal column list.

The Test Data

The test data will contain 1000 account numbers, and 100 random 3 characters for each account. This method utilizes an inline tally table. Please see Jeff Moden's article The "Numbers" or "Tally" Table: What it is and how it replaces a loop at http://www.sqlservercentral.com/articles/T-SQL/62867/ for more information on how a tally table works and what all you can do with it.

-- Conditionally drop the test table to make reruns easier.
IF OBJECT_ID('tempdb..#TestData','U') IS NOT NULL DROP TABLE #TestData;
CREATE TABLE #TestData (AccountNumber INT, 
                        Value CHAR(3));

-- Build 1000 account numbers with random 3 character data.
;WITH
TENS      (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL 
                  SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL 
                  SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),
MILLIONS  (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),
TALLY     (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)
INSERT INTO #TestData
SELECT TOP (100000) 
       AccountNumber = CASE WHEN (N%1000) = 0 THEN 1000 ELSE N%1000 END,
       Value         = CHAR(CONVERT(INT,RAND(CHECKSUM(NEWID()))*10)+64) +
                       CHAR(CONVERT(INT,RAND(CHECKSUM(NEWID()))*10)+64) +
                       CHAR(CONVERT(INT,RAND(CHECKSUM(NEWID()))*10)+64)
  FROM TALLY;

-- Add a clustered index to the table 
CREATE CLUSTERED INDEX IX_#TestData_Cover ON #TestData (AccountNumber, Value);

The Solution (SQL 2005+)

This solution utilizes features introduced in SQL 2005. It starts off with a CTE (common table expression) of all of the distinct AccountNumbers in the table. For each AccountNumber, we get a comma separated list of the Value field, sorted by the Value field.

WITH CTE AS
(
SELECT DISTINCT 
       AccountNumber
  FROM #TestData
)
SELECT AccountNumber,
       CommaList = STUFF((
                   SELECT ',' + Value
                     FROM #TestData
                    WHERE AccountNumber = CTE.AccountNumber
                    ORDER BY Value
                      FOR XML PATH(''), 
                              TYPE).value('.','varchar(max)'),1,1,'')
  FROM CTE
 ORDER BY AccountNumber;

The key to creating the comma separated list is the correlated subquery. Working from the inside out, we get each value prefixed with a comma, order by the Value. The FOR XML PATH('') generates an XML structure, with an empty string as the root node. Since the field is ',' + Value (an unnamed expression), there is no name for the individual elements. What is left is a list of values, with each value prefixed with a comma. The TYPE clause specifies to return the data as an XML type. The .value('.','varchar(max)') takes each value, and converts it into a varchar(max) data type. The combination of the TYPE and .value means that values are created at XML tags (such as the ampersand (&), and the greater than (>) and less than (<) signs), will not be tokenized into their XML representations and will remain as is.

At this point, you will have a comma separated list of all values starting with a comma for each value. All that remains is to remove the very first leading comma from the entire string. To do this, we utilize the STUFF function. Using the string created by the FOR XML PATH(''), TYPE, and starting with the first character, we replace one character (the leading comma) with an empty string. (Note that if you wanted the string to be separated with a comma and a space, you would specify ', ', and replace 2 characters in the STUFF function with an empty string.

The subquery is correlated, meaning that it references a value outside of itself to control what it is doing. In this case, it is referencing the current AccountNumber from the CTE.

The results will look like this (abbreviated due to length)

AccountNumber CommaList
------------- -----------------------------------------------------------------------------
1 @@H,@BE,@CE,@DA,@FA,@FH,@GB,@GD,@HC,@HG,A@E,A@G,AEC,AH@,AHB,AHI,AIG,B@@,B@A,B@B,BBG,B, ...
2 @CB,@CE,@CG,@DB,@EE,@GG,@GG,@HC,@IF,A@E,AAF,AAI,ACG,AEA,AFA,AFB,AFC,AFC,AFI,AGF,AIE,AIH,B ...
3 @@E,@@H,@BE,@CD,@DC,@DI,@EF,@EI,@FB,@GE,A@@,AAE,ACE,AEF,AFA,AGC,AH@,AIH,B@C,BAI,BC@,BDF, ...

Resources:

SQL Spackle - Creating a comma-separated list.docx
Total article views: 28990 | Views in the last 30 days: 51
 
Related Articles
FORUM

SQLSERVER 2000 SQL UNION

SQL UNION

FORUM

working with union all or union

union all vs union

BLOG

Sql Server - Union and Union All

Union and Union All, both are used to select data from one or more than one tables but still they......

FORUM

Union All

sum..Union all

ARTICLE

Union

One of the less used commands in T-SQL, the UNION command can come in very handy in a number of situ...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones