Cancatenation in Select Case statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Cancatenation in Select Case statement

Author

Topic

ATG
Starting Member

35 Posts

Posted - 2010-04-02 : 15:10:26

Hey everyone. I've been programming SQL for about 6 months now and I've encountered something that should be trivial, but I can't seem to figure it out. I'm trying to concatenate items within a case/switch statement. My code currently looks like this without the concat...

[Issues] = case
when GLIDN.JobNotShown = 'Y' then 'The Job is not shown'
when GLIDN.GenLiab2 = 'Y' then 'Coverage is not indicated for General Liability'

I want it to do something like

[Issues] = case
when GLIDN.JobNotShown = 'Y' then [Issues] = [Issues] + 'The Job is not shown'

...but it seems that you cannot access your variable within a statement. What are my options?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 15:25:42

you mean you want to list a big string concatenating values accumulated by checking each row value of table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ATG
Starting Member

35 Posts

Posted - 2010-04-02 : 15:28:58

Yeah. The list is about 15 long and there are cases where more than 1 of the criteria is met. I want it to show all of them rather than just one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 15:30:21

in case of more than one criteria meeting, you need both values concatenated?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ATG
Starting Member

35 Posts

Posted - 2010-04-02 : 15:32:56

It can be even more than 2. I will make them comma separated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 15:36:49

ok.then do something like


DECLARE @Ret varchar(max)

SELECT @Ret=COALESCE(@Ret,'') + case 
when GLIDN.JobNotShown = 'Y' then 'The Job is not shown' else '' end + case 
when GLIDN.GenLiab2 = 'Y' then 'Coverage is not indicated for General Liability' else '' end +...
FROM Table

SELECT @Ret AS YourLongValue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ATG
Starting Member

35 Posts

Posted - 2010-04-02 : 16:00:17

Thanks! It seems like this works, however I'm limited to only 10 levels. I have around 15. Any way around this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-03 : 02:03:53

what does that mean? I've not added any limit so it shouldnt put any limit on results. can you illustrate the problem you're facing?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ATG
Starting Member

35 Posts

Posted - 2010-04-06 : 11:27:21

Ah. Nevermind. Its working now. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 11:47:17

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources