如何修改SQL存储过程，仅填充存在数据的月份以优化报表？

问题背景 #

现有用于自动化报表的SQL存储过程，会向临时表插入12个月的数据，功能正常但报表包含未来月份的大量零值，影响美观。需要修改脚本，仅填充存在实际数据的月份。

解决方案思路 #

原脚本硬编码生成固定13个月份列，不管源表是否有对应月份的数据。改为动态生成表结构和统计语句：

• 先从源表中提取所有存在数据的月份
• 基于这些月份动态创建临时表的列
• 动态生成统计每个有数据月份的SQL语句

修改后的完整脚本 #

declare @sqlCreateTable varchar(max), @sqlInsert varchar(max)
declare @monthList table (MonthCode varchar(6), MonthSeq int)

-- 第一步：从源表获取所有存在数据的月份（去重并排序）
insert into @monthList(MonthCode, MonthSeq)
select distinct LEFT(CONVERT(varchar, datecreated, 112),6) as MonthCode,
       DATEPART(yy, datecreated)*12 + DATEPART(mm, datecreated) as MonthSeq
from TransferDutyReport01.dbo.tmpTDCasesAlerts2
where datecreated is not null
order by MonthSeq

-- 第二步：动态生成创建临时表的SQL
set @sqlCreateTable = '
IF OBJECT_ID(''TransferDutyReport01.dbo.tmpTDReportTable1'', ''U'') IS NOT NULL 
DROP TABLE TransferDutyReport01.dbo.tmpTDReportTable1; 

create table TransferDutyReport01.dbo.tmpTDReportTable1
(
    INDVOL varchar(50),
    INDVAL varchar(50)'

-- 遍历有数据的月份，添加对应列
select @sqlCreateTable = @sqlCreateTable + ',
    [' + MonthCode + '] dec(17,2)'
from @monthList

set @sqlCreateTable = @sqlCreateTable + '
)'

-- 第三步：动态生成插入数据的SQL
set @sqlInsert = '
insert into TransferDutyReport01.dbo.tmpTDReportTable1
(INDVOL'

-- 生成插入的列名列表
select @sqlInsert = @sqlInsert + ', [' + MonthCode + ']'
from @monthList

set @sqlInsert = @sqlInsert + ')
select ''1 Distinct cases'' as INDVOL'

-- 生成每个月份的去重统计语句
select @sqlInsert = @sqlInsert + ',
count(distinct case LEFT(CONVERT(varchar, datecreated, 112),6) = ''' + MonthCode + ''' then ccasenum end) [' + MonthCode + ']'
from @monthList

set @sqlInsert = @sqlInsert + '
from
(
 select *,ROW_NUMBER() over (PARTITION by ccasenum order by datecreated asc)rown from TransferDutyReport01.dbo.tmpTDCasesAlerts2
)qq where rown = 1'

-- 执行动态SQL
exec(@sqlCreateTable)
exec(@sqlInsert)

关键修改说明 #

• 移除原脚本中硬编码的@month1~@month14变量，改为从源表动态提取有数据的月份，避免生成无数据的未来月份列
• 动态生成表结构，仅包含存在数据的月份，彻底消除零值列
• 保留原脚本中按ccasenum去重（rown=1）的核心统计逻辑，确保数据准确性

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