按月份分组统计各项目提交日期数量的实现问题

方法一：SQL层面直接生成交叉统计结果 #

可以通过条件聚合实现按项目分组、按月份列展示提交数量的需求，SQL语句如下：

SELECT 
    Program,
    -- 按月份统计每个项目的提交数
    COUNT(CASE WHEN MONTH(SubmissionDate) = 1 THEN 1 END) AS January,
    COUNT(CASE WHEN MONTH(SubmissionDate) = 2 THEN 1 END) AS February,
    COUNT(CASE WHEN MONTH(SubmissionDate) = 3 THEN 1 END) AS March,
    COUNT(CASE WHEN MONTH(SubmissionDate) = 4 THEN 1 END) AS April,
    COUNT(CASE WHEN MONTH(SubmissionDate) = 5 THEN 1 END) AS May,
    COUNT(CASE WHEN MONTH(SubmissionDate) = 6 THEN 1 END) AS June,
    COUNT(CASE WHEN MONTH(SubmissionDate) = 7 THEN 1 END) AS July,
    COUNT(CASE WHEN MONTH(SubmissionDate) = 8 THEN 1 END) AS August,
    COUNT(CASE WHEN MONTH(SubmissionDate) = 9 THEN 1 END) AS September,
    COUNT(CASE WHEN MONTH(SubmissionDate) = 10 THEN 1 END) AS October,
    COUNT(CASE WHEN MONTH(SubmissionDate) = 11 THEN 1 END) AS November,
    COUNT(CASE WHEN MONTH(SubmissionDate) = 12 THEN 1 END) AS December
FROM [Table]
WHERE YEAR(SubmissionDate) = 2023 -- 直接通过日期字段筛选年份，避免字段歧义
GROUP BY Program
ORDER BY Program;

说明 #

• CASE WHEN 匹配日期对应的月份，COUNT 统计符合条件的提交记录；
• 无提交记录的月份会返回 NULL，如果需要显示为空字符串，可以用 ISNULL(COUNT(...), '') 替换；
• GROUP BY Program 确保结果按项目分组，最终输出格式与你期望的报表结构一致。

方法二：在SSRS中通过矩阵控件实现 #

如果希望在SSRS报表里可视化生成结果，可按以下步骤操作：

1. 保留你的基础查询作为数据集：

   SELECT SubmissionDate, Program FROM [Table] WHERE YEAR(SubmissionDate) = 2023
   

2. 在报表设计界面插入**矩阵（Matrix）**控件；
3. 配置矩阵区域：
   • 将 Program 字段拖到行组区域，作为报表的行维度；
   • 将 DATENAME(MONTH, SubmissionDate) 字段拖到列组区域，作为报表的列维度（若需要按月份顺序排序，可添加 MONTH(SubmissionDate) 作为排序依据）；
   • 将 SubmissionDate 字段拖到数据区域，右键设置聚合函数为 Count；
4. 预览报表即可得到按项目、月份交叉统计的结果，无数据的单元格会自动显示为空。

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