Cool Tips‎ > ‎Data Conversion‎ > ‎

Data Conversion - Very Fast String Unsplitter (CSV String Creator)

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

s:

Author:    ChillyDBA
Date:        5 Jun 2012

Description

This is the reverse of the String Splitter but has no variable delimiter and creates a comma separated values (CSV) string.
The code utilises the COALESCE function to ensure that the last addition to the string has no trailing comma, thus removing the need to strip it off
 
The actual technique is a one-liner, and most of the code below is used to set up and execute test data.
 
This technique can be used in-line, in CTEs and subqueries

Code

-- create some test data
CREATE TABLE #Numbers (number INT, numbertext VARCHAR(20))
INSERT #Numbers (number, numbertext)
SELECT 0, 'Zero'
UNION
SELECT
1, 'One'
UNION
SELECT
2, 'Two'
UNION
SELECT
3, 'Three'
UNION
SELECT
4, 'Four'
UNION
SELECT
5, 'Five'
UNION
SELECT
6, 'Six'
UNION
SELECT
7, 'Seven'
UNION
SELECT
8, 'Eight'
UNION
SELECT
9, 'Nine'


DECLARE @NumberString   VARCHAR(1000)

-- this is the core of the code
-- utilises COALESCE function to ensure no trailing comma on the last addtiiton to the string
SELECT @NumberString = COALESCE (@NumberString + ', ', '') + numbertext FROM #Numbers ORDER BY number

SELECT @NumberString AS NumberString


DROP TABLE #Numbers


ċ
Return comma separated list from multiple rows.sql
(1k)
Andy Hughes,
Jun 23, 2012, 1:22 AM
Comments