Help with tweaking my sql statement (combining values from different columns)

Question

Post reply

Help with tweaking my sql statement (combining values from different columns)

SD-691870

SSC Enthusiast

Points: 144
More actions
August 13, 2008 at 2:37 pm

#191145

I am trying to combine values from different columns to create one comma delimited string.Here is some sample data with the desired output.
I have the sql statement ..and could use some help to tweak it.
Create table #Test
(
SetNo nvarchar(10),
Reason1 nvarchar(50) null,
Reason2 nvarchar(50) null,
Reason3 nvarchar(50) null,
Reason4 nvarchar(50) null
)
Insert into #Test
SELECT 200,'Poor Image','Bad light','Grainy','Distorted'
union all
SELECT 201,'Poor Image',null,null,'Distorted'
union all
SELECT 202,null,null,'Grainy','Distorted'
union all
SELECT 203,null,'Bad light','Grainy',null
union all
SELECT 204,null,'Bad light',null,'Distorted'
--select * from #test
--Desired output:
Set No :200 Poor Image,Bad light,Grainy,Distorted
Set No :201 Poor Image,Distorted
Set No :202 Grainy,Distorted
Set No :203 Bad light,Grainy
Set No :204 Bad light,Distorted
I have this so far..but the the results are incorrect
--without comma
SELECT
Reasons='Set No '+SetNo+ ': '+ COALESCE(Reason1,'') +' '+COALESCE(Reason2,'')+' '+COALESCE(Reason3,'')+' '+COALESCE(Reason4,'')
FROM #test
--with comma
SELECT
Reasons='Set No '+SetNo+ ': '+ COALESCE(Reason1,'') +COALESCE(ISNULL(','+ Reason2 ,''),'')+COALESCE(ISNULL(','+ Reason3,''),'')+COALESCE(ISNULL(','+ Reason4,''),'')
FROM #test
Appreciate your help.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

This should help: http://www.sqlservercentral.com/articles/Test+Data/61572/

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

I think that SD is looking for something less than that, John.

Try this:

Select 'Set No '+SetNo+ ': '

+ CASE When Reasons='' Then ''

When Left(Reasons,1) = ',' Then Substring(Reasons,2,Len(Reasons))

Else Reasons

End as [Reasons]

From (

SELECT SetNo, Reasons=COALESCE(Reason1,'') +COALESCE(','+Reason2 ,'') +COALESCE(','+Reason3,'') +COALESCE(','+ Reason4,'')

FROM #test) t

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

SD-691870 SSC Enthusiast Points: 144 More actions · Answer 3

SD-691870

SSC Enthusiast

Points: 144

August 14, 2008 at 5:43 am

#857968

Thank you so much ...really appreciate it.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Glad I could help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Add this sample data

union all

SELECT 205,null,'',null,''

and trry again.

SELECT'Set No ' + SetNo + ': '

+ SUBSTRING(

ISNULL(',' + NULLIF(Reason1, ''), '')

+ ISNULL(',' + NULLIF(Reason2, ''), '')

+ ISNULL(',' + NULLIF(Reason3, ''), '')

+ ISNULL(',' + NULLIF(Reason4, ''), '')

, 2, 8000)

FROM#Test

N 56°04'39.16"
E 12°55'05.25"

SD-691870 SSC Enthusiast Points: 144 More actions · Answer 6

Never thought of that.

Once again... thank you all wonderful people who have taken the time to respond and steer me in the right direction! 🙂

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Peso (8/14/2008)
Add this sample data
union all
SELECT 205,null,'',null,''

Actually, your examples are out-of-spec Peso and make two additional assumptions: First you assume that zero-length strings are permitted in the first place. This is not necessarily so, many apps and data designs do not permit zero-length strings and coerce them out of the database before they are stored (or as they are stored). As there were no zero-length strings in the examples, but there were a lot of Nulls, I did not add the extra NULLIF/ISNULL transform because it was unnecessary (and technically incorrect) as spec'd.

Secondly, you assume that if there are zero-length strings, then they should be handled like Nulls, when it is entirely possible that the OP wants them to show up as blank values (i.e. ",,"). As spec'd, this is not called for nor correct.

Of course, it is possible that the OP has more requirements than they specified to us...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

On the other hand you are right about how to finesse the Fencepost error out of the string concatenation. I couldn't remember how to do it, which is why I had to resort to the CASE function.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]