I am stuck with a Query in Sql Server 2005?

Question

hi i want to write a query but i don't understand how to solve it

ID  Category    Volume
1   FFP         157
1   RBCD        279
2   FFP         146
2   PLT         70
2   RBCD        280
3   FFP         145
3   PLT         67
3   RBCD        275
4   FFP         148
4   RBCD        280

my result should be like this

ID  Category1   Volume1 Category2   Volume2 Category3   Volume3
1   FFP         157    RBCD          279    NULL        NULL
2   FFP         146    RBCD          280    PLT         70
3   FFP         145    RBCD          275    PLT         67
4   FFP         148    RBCD          280    NULL        NULL

By using Juergen D i am getting like this

ID  Category1   volume1     Category2   volume2 Category3   Volume3
1   FFP         157         NULL        NULL    NULL        Null
1   NULL        NULL        RBCD        279     NULL        Null
2   FFP         146         NULL        NULL    NULL        Null
2   NULL        NULL        RBCD        280     NULL        Null
2   NULL        NULL        NULL        NULL    PLT         70
3   FFP         145         NULL        NULL    NULL        Null
3   NULL        NULL        RBCD        275     NULL        Null
3   NULL        NULL        NULL        NULL    PLT         67
4   FFP         148         NULL        NULL    NULL        Null
4   NULL        NULL        RBCD        280     NULL        Null

Anybody give me any idea how to do this

Answer 1

select id,
       min(case when Category = 'FFP' then Category end) as Category1,
       sum(case when Category = 'FFP' then Volume end) as Volume1,
       min(case when Category = 'RBCD' then Category end) as Category2,
       sum(case when Category = 'RBCD' then Volume end) as Volume2,
       min(case when Category = 'PLT' then Category end) as Category3,
       sum(case when Category = 'PLT' then Volume end) as Volume3
from your_table
GROUP BY id

SQLFiddle demo