The initial table looks this:
| Month | User | LeavesTaken |
|---|---|---|
| 2021-12 | User1 | 1 |
| 2022-01 | User1 | 2 |
| 2022-02 | User1 | 1 |
| 2022-02 | User2 | 2 |
| 2022-02 | User3 | 2 |
Here some users might not have data for few months. How to fill up those days with zero and use it inside the pivot?
The challenge is that the data needs to be pivoted and should look like this:
| Month | User1 | User2 | User3 |
|---|---|---|---|
| 2021-12 | 1 | 0 | 0 |
| 2022-01 | 2 | 0 | 0 |
| 2022-02 | 1 | 2 | 2 |
You can try to use the aggregate condition function with OUTER JOIN
SELECT t1.Month,
SUM(CASE WHEN t2.User ='User1' THEN t2.LeavesTaken ELSE 0 END),
SUM(CASE WHEN t2.User ='User2' THEN t2.LeavesTaken ELSE 0 END),
SUM(CASE WHEN t2.User ='User3' THEN t2.LeavesTaken ELSE 0 END)
FROM (
SELECT DISTINCT Month
FROM T
)t1 LEFT JOIN T t2
GROUP BY t1.Month
with sample_data as (
select '2021-12-01' m , 'User1' u, 1 leaves_taken_in_month
union select '2022-01-01' m , 'User1' u, 2
union select '2022-02-01' m , 'User1' u, 1
union select '2022-02-01' m , 'User2' u, 2
union select '2022-02-01' m , 'User3' u, 2)
select f.M
,zeroifnull( "'User1'")
,zeroifnull( "'User2'")
,zeroifnull( "'User3'")
from
sample_data
pivot
(sum (leaves_taken_in_month)
for
u in ( 'User1','User2','User3' ) ) f ;
