Concatenation and Comma Assistance Required!!!

Question

Post reply

Concatenation and Comma Assistance Required!!!

slr010877

SSC Enthusiast

Points: 123
More actions
June 6, 2016 at 8:53 am

#316907

I have been asked to write a data extract that concatenated the values of 3 fields, each of which are either '1' or '0' entries. These fields need to be ignored if they are '0' or NULL, but written as '1' for field 1, '2' for Field 2 and '3', for Field 3 if a '1' is present in the field.
I have tried to do this a thousand differnent ways now and this seems to be closest to what I am looking for:
CONVERT(CHAR(20),
ISNULL(CASE WHEN Field1 >0 THEN '1' ELSE NULL END
+',','')
+ISNULL(CASE WHEN Field2 >0 THEN '2' ELSE NULL END
+',','')
+ISNULL(CASE WHEN Field3 >0 THEN '3' ELSE NULL END,'')) 'DISPLAYNAME',
However I am left with an annoying comma at the end of most fields:
I.e.
Fields that concatenate as 1,2,3 are perfect
Fields that concatenate as 1, or 1,2, or 2, are all incorrect as they have an extra comma in the entry.
Is there anyone who can help advise someone who has spend far to long trying things and getting nowhere?!!
Help would be most gratefully received!

Viewing 4 posts - 1 through 3 (of 3 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You could use the STUFF function or even the SUBSTRING.

Here's an example using STUFF with different combinations.

SELECT Field1,Field2,Field3,

STUFF( CONVERT(CHAR(20), ISNULL(CASE WHEN Field1 > 0 THEN ',1' ELSE NULL END,'') +

ISNULL(CASE WHEN Field2 > 0 THEN ',2' ELSE NULL END,'') +

ISNULL(CASE WHEN Field3 > 0 THEN ',3' ELSE NULL END,'')), 1, 1, '') 'DISPLAYNAME'

FROM (VALUES(9,9,9),(0,9,9),(9,0,9),(9,9,0),(0,0,9),(0,9,0),(9,0,0),(0,0,0))x(Field1,Field2,Field3)

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

drew.allen SSC Guru Points: 76999 More actions · Answer 2

A little clarification on the reasoning behind Luis' code.

The reason to use the STUFF function, is that it can replace a string of one length with a string of another length: in this case a string with length 1 (',') with a zero-length string ('').

The reason to place the comma before rather than after each entry is that you know that the comma will always appear in position 1 when placed first, but you may need to search for the first occurrence when placed last.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

slr010877 SSC Enthusiast Points: 123 More actions · Answer 3

Thank you both very much, that makes sense and totally solves my issue.

Really appreciated!