Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.