how to sum with case with oracle sql-CodePudding

I m having data in columns as:

item_id	month_in	amount
1	1	1500
1	1	1000
2	1	2500
3	1	2600
3	1	1000
4	1	2700
4	1	1000
1	2	1500
1	2	2000
2	2	1000
3	3	2500
3	3	2500
4	3	1000
4	3	2500

i want to have like this result

item_id	januari	februari	maret
1	2500	3500	0
2	2500	1000	0
3	3600	0	0
4	3700	0	3500

in oracle sql query how to solve this. please help me

i have try this

select item_id, (case month_in=1 then sum(amout) end )AS januari

from table

group by item_id, month_in order by item_id asc

but not working as i expected

CodePudding user response：

Ideally there should be tables which contain all items and all months. Absent that, we can use the following calendar table pivot approach:

SELECT
    i.item_id,
    SUM(CASE WHEN m.month_in = 1 THEN t.amount ELSE 0 END) AS januari,
    SUM(CASE WHEN m.month_in = 2 THEN t.amount ELSE 0 END) AS februari,
    SUM(CASE WHEN m.month_in = 3 THEN t.amount ELSE 0 END) AS maret
FROM (SELECT DISTINCT item_id FROM yourTable) i
CROSS JOIN (SELECT DISTINCT month_in FROM yourTable) m
LEFT JOIN yourTable t
    ON t.item_id = i.item_id AND
       t.month_in = m.month_in
GROUP BY
    i.item_id
ORDER BY
    i.item_id;