Thread: Returning Multiple Values from CASE statement?

Hello,

This is probably an odd request, but I have my reasons :)  Basically, what I need to do is return *multiple* values each for a number of criteria.  Imagine being able to return multiple values for each CASE statement... that's what I'm trying to do.  I can solve this with subqueries, but as you'll see it's *really* ugly, and I'm sure there's a better way (performance is key).  The other way obviously is to repeat the CASE statement for each value I want to return.... but that seems inefficient also.  So I was wondering if someone could take a look at my query and slap me up side the head and set me straight?

 

Here's the kind of query that would *like* to be able to do.....

 

select
  (case when column1 = column2 then column5,column6) as alias5,alias6,
  (case when column3 = column4 then column7,column8) as alias7,alias8
from
  mytable
;

 

and here's the only ugly way I know how to do it......

 

select
  a.*,
  (case a.alias2 = true then b.column5) as alias5,
  (case a.alias2 = true then b.column6) as alias6,
  (case a.alias4 = true then b.column7) as alias7,
  (case a.alias4 = true then b.column8) as alias8
from
  (select
  (case when column1 = column2 then true else false end) as alias2,
  (case when column3 = column4 then true else false end) as alias4
  from
  mytable) a,
  mytable b
where
  a.id = b.id
;

Thanks!!