sql - Joining two tables and getting rows as columns -

i have 2 tables. 1 transaction table of user id following details.

user_id product_id timestamp transaction_id 123     a_1                   id1 123     a_2                   id1 124     a_1                   id2  125     a_2 

now there product_id mapping division product belongs

mapping:

product_id   division a_1          grocery a_2          electronics , on 

i need final table have 1 record each user id , corresponding items bought in each division separate columns.

user_id   grocery  electronics 123        1       1 124        1       0   

i did this:

select user_id, case (when division ="grocery" count(product_id) else 0) end grocery  when division="electronics" count(product_id) else 0) end electronics   ( select user_id, a.product_id, b.division transact left join  mapping b on a.product_id=b.product_id )  group user_id  sound good? 

when use conditional aggregation, case argument aggregation function:

select user_id,        sum(case when m.division = 'grocery' 1 else 0 end) grocery,         sum(case when m.division = 'electronics' 1 else 0 end) electronics  transact t left join       mapping m      on t.product_id = m.product_id group user_id 

sql fiddle

in addition:

• your table aliases should abbreviations table. makes code easier understand.
• you don't need subquery.
• use single quotes string constants. sql standard.