SQL Query - Convert data values into attributes into antother table. -

i building report , stuck formulating query. bringing following data multiple tables after lot of joins.

id        type    rating -----     ----    ------ id_r1           1 id_r1     b       3 id_r2           2 id_r2     b       1 id_r3           4 id_r3     b       4 id_r4           2 id_r4     b       3 id_r5           2 id_r5     b       3 

what happening every id have rating type & b need transform above following

id       type_a_rating    type_b_rating -----    -------------    ------------- id_r1    1                3 id_r2    3                1 id_r3    4                4 id_r4    2                3 id_r5    2                3 

i have think group , different techniques far unable come solution. need f1! f1!

p.s record end game getting count of (a,b) combinations

type_a_rating    type_b_rating    count -------------    -------------    ----- 1                1                0 1                2                0 1                3                1 1                4                0 2                1                0 2                2                0 2                3                2 2                4                0 3                1                1 3                2                0 3                3                0 3                4                0 4                1                0 4                2                0 4                3                0 4                4                1 

from can see simple group by form and or conditions doesn't suffice until data mentioned. use 2 intermediate/temp tables, in 1 type_a_rating id , in second type_b_rating id , in combine both isn't there better way.

this should work sql engine agnostic solution (provided there 1 row type each id , 1 row type b each id):

select   ta.id,   ta.rating type_a_rating,   tb.rating type_b_rating (select id, rating  t type = 'a') ta inner join (select id, rating   t type = 'b') tb on ta.id = tb.id 

related sql fiddle: http://sqlfiddle.com/#!9/7e6fd9/2

alternative (simpler) solution:

select   id,   sum(case when type = 'a' rating else 0 end) type_a_rating,   sum(case when type = 'b' rating else 0 end) type_b_rating   t group   id 

fiddle: http://sqlfiddle.com/#!9/7e6fd9/3

edit:

the above correct both can simplified bit:

select ta.id, ta.rating type_a_rating, tb.rating type_b_rating t ta join      t tb      on ta.id = tb.id , a.type = 'a' , b.type = 'b'; 

and (because prefer null when there no matches:

select id,        max(case when type = 'a' rating end) type_a_rating,        max(case when type = 'b' rating end) type_b_rating t group id