How to create a comma delimited string

Question

Post reply

How to create a comma delimited string

marty.seed

SSCrazy

Points: 2442
More actions
May 24, 2011 at 3:07 pm

#236787

Hello,
Here is a sample data I am using.
CREATE TABLE #temp
(ae1 varchar,
ae2 varchar,
ae3 varchar,
ae4 varchar)
INSERT INTO #temp SELECT 1,2,null,null
INSERT INTO #temp SELECT null,NULL,3,4
INSERT INTO #temp SELECT null,null,NULL,4
INSERT INTO #temp SELECT NULL,NULL,3,null
INSERT INTO #temp SELECT 1,null,null,null
I need to return this as
ae1ae2ae3ae4Error Codes
12NULLNULL1, 2
NULLNULL343, 4
NULLNULLNULL44
NULLNULL3NULL3
1NULLNULLNULL1
I can't seem to figure out how to concatenate all the fields properly, especially when there are null values
I tried Stuff() but wasn't having much luck although I figure my syntax was wrong.
Any hints are appreciated!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182275 More actions · Answer 1

There may be a more elegant solution that looks and/or performs better but this query gives the results you asked for:

CREATE TABLE #temp

(

ae1 VARCHAR,

ae2 VARCHAR,

ae3 VARCHAR,

ae4 VARCHAR

)

INSERT INTO #temp SELECT 1,2,null,null

INSERT INTO #temp SELECT null,NULL,3,4

INSERT INTO #temp SELECT null,null,NULL,4

INSERT INTO #temp SELECT NULL,NULL,3,null

INSERT INTO #temp SELECT 1,null,null,null

WITH cte(ae1, ae2, ae3, ae4, concat_string)

AS (

SELECT ae1,

ae2,

ae3,

ae4,

ISNULL(ae1 + ',', '') + ISNULL(ae2 + ',', '') + ISNULL(ae3 + ',', '') + ISNULL(ae4 + ',', '') AS concat_string

FROM #temp

)

SELECT ae1,

ae2,

ae3,

ae4,

LEFT(concat_string, LEN(concat_string) - 1) AS concat_string

FROM cte ;

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

WayneS SSC Guru Points: 95404 More actions · Answer 2

whoops... opc.three has it covered.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Orlando Colamatteo SSC Guru Points: 182275 More actions · Answer 3

One tweak I would make to mine is to trade in the two separate function calls to LEFT and LEN for one call to STUFF:

CREATE TABLE #temp

(

ae1 VARCHAR,

ae2 VARCHAR,

ae3 VARCHAR,

ae4 VARCHAR

) ;

INSERT INTO #temp SELECT 1,2,NULL,NULL ;

INSERT INTO #temp SELECT NULL,NULL,3,4 ;

INSERT INTO #temp SELECT NULL,NULL,NULL,4 ;

INSERT INTO #temp SELECT NULL,NULL,3,NULL ;

INSERT INTO #temp SELECT 1,NULL,NULL,NULL ;

INSERT INTO #temp SELECT NULL,NULL,NULL,NULL ;

WITH cte ( ae1, ae2, ae3, ae4, concat_string )

AS (

SELECT ae1,

ae2,

ae3,

ae4,

ISNULL(',' + ae1, '') + ISNULL(',' + ae2, '') + ISNULL(',' + ae3, '') + ISNULL(',' + ae4, '') AS concat_string

FROM #temp

)

SELECT ae1,

ae2,

ae3,

ae4,

STUFF(concat_string, 1, 1, '') AS concat_string

FROM cte ;

Edit

PS and at that point who needs the cte?

SELECT ae1,

ae2,

ae3,

ae4,

STUFF(ISNULL(',' + ae1, '') + ISNULL(',' + ae2, '') + ISNULL(',' + ae3, '') + ISNULL(',' + ae4, ''), 1, 1, '') AS concat_string

FROM #temp ;

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

marty.seed SSCrazy Points: 2442 More actions · Answer 4

Thanks so much, I knew I was close but the last post really cleared up how the STUFF function works!