String Together Mulitple fields when not NULL

Question

String Together Mulitple fields when not NULL

rjjh78

SSCrazy

Points: 2784
More actions
April 26, 2016 at 2:38 pm

#313537

Hello,
I am looking for help to sting together multiple fields that will separate data with commas, eliminate the last comma, and only list records that are not NULL. Can someone help me understand how I can do this?
Here is my sample code followed by the desired outcome:
CREATE TABLE #Fields
(
IDint identity(1,1) not NULL,
Test1varchar(20),
Test2varchar(20),
TEst3varchar(20),
Test4varchar(20),
Test5varchar(20),
Test6varchar(20),
Test7varchar(20),
Test8varchar(20),
Test9varchar(20),
Test10varchar(20)
)
INSERT INTO #fields (Test1,Test2,Test3,Test4,Test5,Test6,Test7,Test8,Test9,Test10) VALUES ('1000','1001','1002','1003','1004','1005','1006','1007','1008','1009')
INSERT INTO #fields (Test1,Test2,Test3,Test4,Test5,Test6,Test7,Test8,Test9,Test10) VALUES ('1010','1011','1012',NULL,NULL,NULL,'1016','1017','1018','1019')
INSERT INTO #fields (Test1,Test2,Test3,Test4,Test5,Test6,Test7,Test8,Test9,Test10) VALUES (NULL,'1021','1022','1023','1024','1025','1026','1027','1028','1029')
SELECT * FROM #Fields
Desired Outcome:
Row 1: 1000,1001,1002,1003,1004,1005,1006,1007,1008,1009
Row 2: 1010,1011,1012,1016,1017,1018,1019
Row 3: 1021,1022,1023,1024,1025,1026,1027,1028,1029
Thank you in advance!

Viewing 7 posts - 1 through 6 (of 6 total)

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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

This will get you there except when the first or last fields are NULL. I'll let you figure that out or perhaps someone else will jump in to seal the deal.

SELECT ID, CC =

REPLACE(REPLACE(REPLACE(REPLACE(

(

SELECT ISNULL(Test1,'')+','+ISNULL(Test2,'')+','+ISNULL(Test3,'')+','+

ISNULL(Test4,'')+','+ISNULL(Test5,'')+','+ISNULL(Test6,'')+','+ --,Test2,Test3,Test4,Test5,Test6,Test7,Test8,Test9,Test10

ISNULL(Test7,'')+','+ISNULL(Test8,'')+','+ISNULL(Test9,'')+','+

ISNULL(Test10,'')

FROM #Fields f2

WHERE f1.ID = f2.ID

FOR XML PATH('')

),',,',','),',,',','),',,',','),',,',',')

FROM #Fields f1;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 2

The fact that, unless you've tattooed the server settings, Anything + NULL = NULL makes this fairly easy.

SELECT ID,

STUFF(

ISNULL(','+Test1,'')

+ ISNULL(','+Test2,'')

+ ISNULL(','+Test3,'')

+ ISNULL(','+Test4,'')

+ ISNULL(','+Test5,'')

+ ISNULL(','+Test6,'')

+ ISNULL(','+Test7,'')

+ ISNULL(','+Test8,'')

+ ISNULL(','+Test9,'')

+ ISNULL(','+Test10,'')

,1,1,'')

FROM #Fields

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rjjh78 SSCrazy Points: 2784 More actions · Answer 3

rjjh78

SSCrazy

Points: 2784

April 27, 2016 at 8:19 am

#1874951

thank you very much!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Or if you're on 2012, you could use CONCAT.

SELECT ID,

STUFF(

CONCAT( ',' + Test1

,',' + Test2

,',' + Test3

,',' + Test4

,',' + Test5

,',' + Test6

,',' + Test7

,',' + Test8

,',' + Test9

,',' + Test10)

,1,1,'')

FROM #Fields;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61136 More actions · Answer 5

rjjh78 (4/27/2016)
thank you very much!

Make sure not to use my solution (not my best work - but I was in a hurry). Both Jeff and Luis posted brilliant solutions. I learned something new looking at Luis' code.