SQL Server含NULL数据透视表：生成三年动态业绩报表

需求说明 #

需要生成一份动态业绩报表，覆盖连续三年的数据（例如2024年生成报表时，需包含2022、2023、2024年数据）。现有样本数据最早始于2022年7月，但报表必须展示全年12个月份，无数据的位置显示NULL。

样本数据 #

Year        Month   Amount
2022        07      2207
2022        08      2208
2022        09      2209
2022        10      2210
2022        11      2211
2022        12      2212
2023        01      2301    
2023        02      2302

期望报表样式 #

2021    2022    2023    
01  Null    Null    2301
02  Null    Null    2302
03  Null    Null    Null
04  Null    Null    Null
05  Null    Null    Null
06  Null    Null    Null
07  Null    2207    Null
08  Null    2208    Null
09  Null    2209    Null
10  Null    2210    Null
11  Null    2211    Null    
12  Null    2212    Null

SQL解决方案（John提供） #

create table temp
(
    [Year] CHAR(4),
    [Month] CHAR(2),
    amount money
)

insert into temp values ('2022', '07', 2207)
insert into temp values ('2022', '08', 2208)
insert into temp values ('2022', '09', 2209)
insert into temp values ('2022', '10', 2210)
insert into temp values ('2022', '11', 2211)
insert into temp values ('2022', '12', 2212)
insert into temp values ('2023', '01', 2301)
insert into temp values ('2023', '02', 2302)

Declare @SQL varchar(max) = '
Select *
 From  (
        Select Item = [Year]
              ,[Month]
              ,Amount
         From temp
         Where [Year]>=year(getdate())-2
         Union All
        Select Item = year(getdate())
              ,[Month] 
              ,Amount = null
         From ( values (''01''),(''02''),(''03''),(''04''),(''05''),(''06''),(''07''),(''08''),(''09''),(''10''),(''11''),(''12'') ) A([Month])
       ) src
 Pivot ( sum(Amount) for item in ('+ quotename(year(getdate())-2)+','+quotename(year(getdate())-1)+','+quotename(year(getdate())-0) +') ) pvt
'
Exec(@SQL)

drop table temp

执行结果 #

执行后的报表与期望样式一致，完整展示了12个月份的连续三年数据，无记录的月份对应位置显示NULL，满足动态生成报表的需求。

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