select year(datecol) as year
,sum(case when month(datecol) = 1 then 1 else 0 end) as jan
,sum(case when month(datecol) = 2 then 1 else 0 end) as feb
,sum(case when month(datecol) = 3 then 1 else 0 end) as mar
,sum(case when month(datecol) = 4 then 1 else 0 end) as apr
,sum(case when month(datecol) = 5 then 1 else 0 end) as may
,sum(case when month(datecol) = 6 then 1 else 0 end) as jun
,sum(case when month(datecol) = 7 then 1 else 0 end) as jul
,sum(case when month(datecol) = 8 then 1 else 0 end) as aug
,sum(case when month(datecol) = 9 then 1 else 0 end) as sep
,sum(case when month(datecol) = 10 then 1 else 0 end) as oct
,sum(case when month(datecol) = 11 then 1 else 0 end) as nov
,sum(case when month(datecol) = 12 then 1 else 0 end) as dec
from datetest
group by year(datecol)
order by 1;
这将给予类似于以下内容的输出:
YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2018 0 0 0 0 0 0 0 0 0 0 3 0
2019 0 0 0 0 0 0 0 0 0 1 2 0
2020 0 0 0 0 0 0 0 0 0 1 1 0
2021 0 0 0 0 0 0 0 0 0 2 6 0
2条答案
按热度按时间ssm49v7z1#
由于月份是一个已知的量,因此可以使用case语句的求和:
这将给予类似于以下内容的输出:
sxpgvts32#
您可以使用链接dynamic pivot SQL Query in Db2中描述的通用例程。
使用以下调用获取案例所需的结果集:
其结果是:
| 年份|十一月|十月|
| - -|- -|- -|
| 小行星2021|六个|2个|