case when multiple results - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

case when multiple results

Author

Topic

pr4t3ekd
Starting Member

31 Posts

Posted - 2010-02-05 : 03:14:34

Hi,
is there way for the following to return multiple results? If not, what is another solution.

CASE item when 'test' then 'good','very good'
when 'test2' then 'bad','very bad'

or something? cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-05 : 03:35:07

you have to use 2 case statement


case item when 'test' then 'good'
          when 'test2' then 'bad'
          end,
case item when 'test' then 'very good'
          when 'test2' then 'very bad'
          end,

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 03:57:09

Or concatenate them?


COALESCE(case item when 'test' then 'good'
          when 'test2' then 'bad'
          end, '')
+ COALESCE(',' + case item when 'test' then 'very good'
          when 'test2' then 'very bad'
          end, '')

not very elegant, and if the first one returns nothing there will be an unnecessary leading ",", .... but might be basis for a more elegant solution?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-05 : 04:21:46

if just to return the concatenate string, you don't need 2 case statement right ?


case item when 'test' then 'good, very good'
          when 'test2' then 'bad, very bad'
          end,

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 04:26:05

Good point! I was assuming a more complex real-world case, but the pair of values were based on a single test, so not relevant.

Perhaps I'll work on a suitable complex UNION ALL example answer?!!

Perhaps I'll put that, too, on hold until the coffee starts to work!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-05 : 04:33:34

Ah . . . I see. I guess that is too complex for my brain (even with coffee) to think of that right now . . . almost end of the day .. running out of steam soon

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-05 : 06:22:03

quote:
Originally posted by Kristen

Or concatenate them?

COALESCE(case item when 'test' then 'good'
when 'test2' then 'bad'
end, '')
+ COALESCE(',' + case item when 'test' then 'very good'
when 'test2' then 'very bad'
end, '')

not very elegant, and if the first one returns nothing there will be an unnecessary leading ",", .... but might be basis for a more elegant solution?

What about this?


case item when 'test' then '''good'', ''very good'''
          when 'test2' then '''bad'', ''very bad'''
          end

Madhivanan

Failing to plan is Planning to fail

pr4t3ekd
Starting Member

31 Posts

Posted - 2010-02-05 : 08:07:20

Hey guys, thanks for your help but i now i'm thinking that i did not make myself clear.

i'll try again:-

query again below:

SELECT
case when sale = 'Accounts' AND Gas = 'Transfer' AND Elec = 'Transfer' THEN 'Accounts-Gas'
when sale = 'Accounts' AND Gas = 'Transfer' AND Elec = 'Transfer' THEN 'Accounts-Elec'
end
FROM table
where id = '1'

based on the above, i would two results, one being 'Accounts-Gas' and 'Accounts-Elec' as the case is the same in both scenerio's but just a different result ('accounts-elec' or 'accounts-gas')

hope this makes sense?!

cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-05 : 08:11:11

SELECT
case when sale = 'Accounts' AND Gas = 'Transfer' AND Elec = 'Transfer' THEN '''Accounts-Gas'' or ''Accounts-Elec''' end
FROM table
where id = '1'

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 08:13:51

"i would two results"

No, you only get the first result from a CASE statement (the first one that matches your WHEN criteria).

The fact that subsequent WHEN criteria might ALSO be true is ignored, only the first is used.

pr4t3ekd
Starting Member

31 Posts

Posted - 2010-02-05 : 08:28:06

so what other way is there for me to get two results if i was to not use a case statement...

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 08:30:54

As KHTAN had earlier - two result set columns:


case item when 'test' then 'good'
          when 'test2' then 'bad'
          end AS Result1,
case item when 'test' then 'very good'
          when 'test2' then 'very bad'
          end AS Result2,

pr4t3ekd
Starting Member

31 Posts

Posted - 2010-02-05 : 08:32:26

madhivanan, 'OR' does not work..

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 08:35:47

"what other way is there"

I wanted to generate a data transfer file to accounts yesterday. The fileneeded a row outputting for "normal" transactions, and two rows outputting for "Markup" transactions (markup has already been applied, so one with the original amount (80% of value), and the second with the markup (20% of value)).

So I did


FROM MyTable
JOIN ... various tables ...
JOIN
(
    SELECT 1 AS [RowNumber] UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
)
    ON ([RowNumber] = 1 AND TransactionType <> 'MARKUP')
    OR ([RowNumber] > 1 AND TransactionType =  'MARKUP')

that gave me either a single row with either RowNumber=1, or a pair or rows with RowNumber=1 or RowNumber=2

pr4t3ekd
Starting Member

31 Posts

Posted - 2010-02-05 : 08:41:58

"case item when 'test' then 'good'
when 'test2' then 'bad'
end AS Result1,
case item when 'test' then 'very good'
when 'test2' then 'very bad'
end AS Result2,
"

i guess i'm just going to have to use this and use this as a view and do another case statement from result1, result2 column and then make it into one column :(.. too messy but i guess that's what has to be done if i want it in one column. thanks guys

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 08:53:14

Well if you want them in ONE column then just concatenate them as per my earlier suggest (i.e. "Posted - 02/05/2010 : 03:57:09")

Or is there some problem with that approach?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 09:04:31

quote:
Originally posted by pr4t3ekd

"case item when 'test' then 'good'
when 'test2' then 'bad'
end AS Result1,
case item when 'test' then 'very good'
when 'test2' then 'very bad'
end AS Result2,
"

i guess i'm just going to have to use this and use this as a view and do another case statement from result1, result2 column and then make it into one column :(.. too messy but i guess that's what has to be done if i want it in one column. thanks guys

i didnt understand what exactly you're trying to do but based on your explanation above shouldnt this be enough


case item when 'test' then 'good'
          when 'test2' then 'bad'
          end +
case item when 'test' then 'very good'
          when 'test2' then 'very bad'
          end AS Result2

which is similar to what Kristen suggested earlier

pr4t3ekd
Starting Member

31 Posts

Posted - 2010-02-05 : 09:08:25

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 09:11:48

quote:
Originally posted by pr4t3ekd

it's not concatenation what i want. I want the same row twice but the only difference is that column. do you know what i mean?

so like eg results according to you would be:
ID | Result |
1 | 'Accounts-Elec','Accounts-Gas' |

but what i am actually after is:-

ID | Result |
1| 'Accounts-Elec'|
1| 'Accounts-Gas'|

....

what will determine if its Accounts-Elec or Accounts-Gas that needs to be returned?

pr4t3ekd
Starting Member

31 Posts

Posted - 2010-02-05 : 09:14:22

there may not be a difference like so:

SELECT
case when sale = 'Accounts' AND Gas = 'Transfer' AND Elec = 'Transfer' THEN 'Accounts-Gas'
when sale = 'Accounts' AND Gas = 'Transfer' AND Elec = 'Transfer' THEN 'Accounts-Elec'
end
FROM table
where id = '1'

but i need two records instead of one.

ps: this is a little question to my bigger sql statement so whilst you may be saying to yourself 'but why'... with the approach i have taken, i do need two records instead of the one...

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 09:17:33

OK then as per my comment "Posted - 02/05/2010 : 08:35:4", or use a UNION ALL of the SELECT ... FROM with the alternate CASE statements in each of the two SELECTs

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources