Very Fast String Unsplitter (CSV String Creator)

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Author: ChillyDBA

Date: 5 Jun 2012

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

-- 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

Page updated

Google Sites

Report abuse