SQL Pivot（月份为列头）：如何将查询结果调整为按年份单行展示

我编写了如下SQL语句，用于统计schema.app_table中各年份不同月份的distinct app_id数量：

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)

当前查询能得到正确数值，但格式不符合预期：每个月份单独占一行，其他月份显示0。我希望实现如下格式的结果：

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

解决方案 #

调整后的SQL语句如下：

select year(recvd_date) as recvd_year, 
MAX(case when month(recvd_date)=1 then count(distinct(app_id)) else 0 end) as Jan,
MAX(case when month(recvd_date)=2 then count(distinct(app_id)) else 0 end) as Feb,
MAX(case when month(recvd_date)=3 then count(distinct(app_id)) else 0 end) as Mar,
MAX(case when month(recvd_date)=4 then count(distinct(app_id)) else 0 end) as Apr,
MAX(case when month(recvd_date)=5 then count(distinct(app_id)) else 0 end) as May,
MAX(case when month(recvd_date)=6 then count(distinct(app_id)) else 0 end) as Jun,
MAX(case when month(recvd_date)=7 then count(distinct(app_id)) else 0 end) as Jul,
MAX(case when month(recvd_date)=8 then count(distinct(app_id)) else 0 end) as Aug,
MAX(case when month(recvd_date)=9 then count(distinct(app_id)) else 0 end) as Sep,
MAX(case when month(recvd_date)=10 then count(distinct(app_id)) else 0 end) as Oct,
MAX(case when month(recvd_date)=11 then count(distinct(app_id)) else 0 end) as Nov,
MAX(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)

调整说明： #

1. 移除GROUP BY中的month(recvd_date)：将分组维度仅保留年份，确保每一年的统计结果只占一行。
2. 用MAX()包裹每个月份的统计表达式：按年份分组后，每个月份的CASE语句仅在对应月份有有效统计值，其他月份为0，取最大值可以保留对应月份的真实统计数，过滤掉无效的0值。
3. 若某年份无对应月份的数据，该月份会显示0，符合需求。

内容的提问来源于stack exchange，提问作者learner