Return multiple values via multiple select/case and add to variables

[iluvwitter]

Hi,

I am trying to return multiple values using select/case statements and include those values in a text message and send via sql mail.

The table will display which jobs have ran via codes. I want to gather all the codes that ran today and substitute the codes with a different value.

Let's say dataset is:
Time Code
2009-07-22 17:35:02:597 A
2009-07-22 19:35:02:597 C

I am not sure how to go about this. So if A and C were run today, then the query would return: "Report Apple and Report Cat were run today". I think I would need to use some sort of text variable for each code returned today and send the text variable via SQL mail. Originally I created one select statement with several case...when statements but im not sure how i actually take the values, substitue the codes with a new value (Report Apple or Report Cat), and generate a text message with the new values.

Any help is appreciated. Thanks!

 

[PDreyer]

Already answered in your other post:
Don't use case statement to store data in your code see

http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx

 

[iluvwitter]

The issue is, I can't modify the table. It is an existing table so I need to store values in code to get the result I need????

 

[RyanEK]

Hi,

You can use case statements to map each code to a description eg.

declare @test table (code varchar(1))
insert into @test values ('A')
insert into @test values ('C')
insert into @test values ('D')

select code,
case when code = 'A' then 'Report_Apple'
when code = 'C' then 'Report_Cat'
when code = 'D' then 'Report_Dog' end as codetext
from @test

Using this query in a subtable and XML Path for, you can merge the results.

select 
replace(replace ((
 select codetext as 'data()'
 from (
  select code,
  case when code = 'A' then 'Report_Apple'
  when code = 'C' then 'Report_Cat'
  when code = 'D' then 'Report_Dog' end as codetext 
  from @test
 ) as t1
 for xml path('')),' ',' and '),'_',' ')

Your result should be

Report Apple and Report Cat and Report Dog

Ryan