SQL Pivot – Months as Column headers

June 26, 2023

I have a SQL written as below:

select year(recvd_date), 
(case when month(recvd_date)=1 then count(distinct(app_id)) else 0 end) as Jan,
(case when month(recvd_date)=2 then count(distinct(app_id)) else 0 end) as Feb,
(case when month(recvd_date)=3 then count(distinct(app_id)) else 0 end) as Mar,
(case when month(recvd_date)=4 then count(distinct(app_id)) else 0 end) as Apr,
(case when month(recvd_date)=5 then count(distinct(app_id)) else 0 end) as May,
(case when month(recvd_date)=6 then count(distinct(app_id)) else 0 end) as Jun,
(case when month(recvd_date)=7 then count(distinct(app_id)) else 0 end) as Jul,
(case when month(recvd_date)=8 then count(distinct(app_id)) else 0 end) as Aug,
(case when month(recvd_date)=9 then count(distinct(app_id)) else 0 end) as Sep,
(case when month(recvd_date)=10 then count(distinct(app_id)) else 0 end) as Oct,
(case when month(recvd_date)=11 then count(distinct(app_id)) else 0 end) as Nov,
(case when month(recvd_date)=12 then count(distinct(app_id)) else 0 end) as Dec
from schema.app_table
--where year(recvd_date) = 2023
group by year(recvd_date), month(recvd_date)

It is giving me the right numbers but the format is not exactly what I expect. The numbers appear on different rows and zero filled for other months. How can I achieve the below format.

Expected Format:

year(recvd_date)    Jan     Feb     Mar     Apr     May     Jun     Jul
2023                66      45      21      22      10      9       8

>Solution :

This can be done using the conditional aggregation :

select year(recvd_date), 
count(distinct case when month(recvd_date) = 1 then app_id end) as Jan,
count(distinct case when month(recvd_date) = 2 then app_id end) as Feb,
....
from schema.app_table
-- where year(recvd_date) = 2023
group by year(recvd_date)

Tested on mysql here : https://dbfiddle.uk/WbY_jC_W