Oracle Case when problem

Question

I Have a table like fallowing

    work_id      group    effort
   -----------   -----   --------
       1           A        10
       1           B        5
       2           A        7
       2           C        20
       2           B         3

I need a case when query that returns like fallowing

   work_id       1.group   effort1   2.Group  effort2    3.group  effort3
   -----------   -----     --------  -------  ------    -------  ------
       1           A        10        B        5
       2           A        7         C        20        B         3

goups are grouped based on another parameter that in another table. But when i write my query like

case when <condition1> then group end as 1.group
case when <condition2> then group end as 2.group
case when <condition4> then group end as 3.group

...(Same for efforts as well)

it returns

work_id       1.group   effort   2.Group  effort    3.group  effort
-----------   -----   --------   -------  ------    -------  ------
    1           A        10        null     null      null     null
    1           null     null     B        5
    2           A        7         null     null    null     null
    2           null     null                          C      20                      
    2           null     null      null      null     B         3

Can anyone help?

Answer 1

You are trying to write a PIVOT query. If you're trying to use a CASE to do so (which implies that you are not on 11g where you can simply use the PIVOT operator), you need to do an aggregate

SELECT work_id,
       MAX( CASE WHEN <<condition1>> then group else null end ) as "1.group",
       MAX( CASE WHEN <<condition2>> then group else null end ) as "2.group",
       MAX( CASE WHEN <<condition3>> then group else null end ) as "3.group",
       MAX( CASE WHEN <<condition1>> then effort else null end ) as "1.effort",
       MAX( CASE WHEN <<condition2>> then effort else null end ) as "2.effort",
       MAX( CASE WHEN <<condition3>> then effort else null end ) as "3.effort"
  FROM <<table name>>
 GROUP BY work_id

Of course, you presumably don't actually have a column named GROUP in your table since that is a reserved word.

Answer 2

Try to take the MIN() of each of your CASE statements. If you can work this into a PIVOT, even better.