Pivot using join, case when, and to char in Oracle doesn't show the right result-CodePudding

So I wanna join these table:

Table A:

Name	ID
James	01
Jonah	02

Table B:

ID	Score	Date
01	80	12-Nov-2022
01	90	24-Dec-2022
02	70	12-Nov-2022
02	60	24-Dec-2022

Select result that I want:

Name	Score of Nov	Score of Dec
James	80	90
Jonah	70	60

Select result that I get:

Name	Score of Nov	Score of Dec
James	0	0
Jonah	0	0

This is my query: `

select a.name,
       sum(case when b.date='Nov' then B.Score else 0 end) as Nov,
       sum(case when b.date='Dec' then B.Score else 0 end) as Dec
from table_a a
join table_b b on a.id=b.id
group by a.name;

I also tried: `

select a.name,
       sum(case when To_char(b.date,'dd-mon-yyyy')='Nov' then B.score else 0 end) as NOv,
       sum(case when To_char(b.date,'dd-mon-yyyy')='Dec' then B.score else 0 end) as Dec
from table_a a
join table_b b on a.id=b.id
group by a.name;

but to no luck. idk how to put the where in to char and CASE WHEN, especially I just want to take the month. Any ideas?

CodePudding user response：

You can't compare a date with the 'Nov' and 'Dec' strings, you should rather extract the month from the date and apply the comparison:

SELECT a.Name,
       SUM(CASE WHEN EXTRACT(MONTH FROM b.Date_)=11 THEN B.Score ELSE 0 END) as Nov_,
       SUM(CASE WHEN EXTRACT(MONTH FROM b.Date_)=12 THEN B.Score ELSE 0 END) as Dec_
FROM       table_a a
INNER JOIN table_b b
        ON a.ID = b.ID
GROUP BY  a.Name

Check the demo here.