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
|
