如何在T-SQL中使用PIVOT实现按列统计各分类课程总数

问题描述 #

• 学习T-SQL复杂PIVOT用法时遇到障碍，参考官方文档、SQLShack等公开教程后仍未得到预期结果
• 在SSRS中搭建多参数筛选报表，需要将每个参数的可选值单独作为列展示计数，避免手动逐行累加同分类数值
• 当前异常：报表预览时同分类的数值分散在不同行，例如下图中统计标记为DNA的课程总数，需要手动累加第3行和第6行的数值才能得到结果；不需要逐行展示的总计值，需要让CASE语句生成的每个分类标签对应独立列，直接展示各分类总计数
  

现有问题代码 #

@StartDate AS DATE
, @EndDate AS DATE
, @Car AS VARCHAR(MAX)
, @DriverType AS VARCHAR(MAX)
, @Attendance AS VARCHAR(MAX)
, @LearnerType AS VARCHAR(MAX)

DROP TABLE IF EXISTS #AttendanceBreakdown, #Final, #Months

SELECT DISTINCT [YearMonth]
INTO #Months
FROM xxx
WHERE [Date] BETWEEN @StartDate AND @EndDate

SELECT [Car_code]
, [Car_code] + ' - ' + S.[Car_name] AS [Car]
, [YearMonth]
, CASE 
    WHEN [attendance] = 'Att' THEN 'Attended' 
    WHEN [attendance] = 'Can' THEN 'Cancelled' 
    WHEN [attendance] = 'Ns' THEN 'No Show' 
END AS [attendance]
, CASE 
    WHEN [driver_type] = 'L' THEN 'Learning'
    WHEN [driver_type] = 'M' THEN 'Matured'
    WHEN [driver_type] = 'F' THEN 'First'
    WHEN [driver_type] = 'C' THEN 'Experienced'
END AS [driver_type]
, CASE 
    WHEN [learner_type] = 'N' THEN 'New' 
    WHEN [learner_type] = 'C' THEN 'Current' 
END AS [learner_type]
INTO #AttendanceBreakdown
FROM [xxx]
LEFT JOIN [xxx].[xxx].[xxx] AS C ON C.[Date] = [xxx]
LEFT JOIN [xxx].[xxx].[xxx] AS S ON S.[car_code] = [car_code]
WHERE [xxx] BETWEEN @StartDate AND @EndDate
AND [car_code] IN (SELECT [StringLiteral] FROM [xxx].[xxx].[fnSplitStringList] (@Car))
AND [xxx] IS NOT NULL

 SELECT M.[YearMonth]
, [car_code]
, T.[Car]
, [attendance]
, [driver_type]
, [learner_type]
, COUNT(1) AS [TotalLessons]
INTO #Final
FROM #Months AS M
LEFT JOIN #AttendanceBreakdown AS T ON T.[YearMonth] = M.[YearMonth]
WHERE [car_code] IN (SELECT [StringLiteral] FROM [xxx].[xxx].[fnSplitStringList] (@Car))
AND [driver_type] IN (SELECT [StringLiteral] FROM [xxx].[xxx].[fnSplitStringList] (@DriverType))
AND [attendance] IN (SELECT [StringLiteral] FROM [xxx].[xxx].[fnSplitStringList] (@Attendance))
AND [learner_type] IN (SELECT [StringLiteral] FROM [xxx].[xxx].[fnSplitStringList] (@LearnerType))
GROUP BY M.[YearMonth]
, [car_code]
, T.[Car]
, [attendance]
, [driver_type]
, [learner_type]


SELECT F.[YearMonth]
, F.[Car]
, [attendance]
, [driver_type]
, [learner_type]
, [TotalLessons]
FROM #Final AS F
ORDER BY [Car]
, [YearMonth] DESC
, [driver_type]
, [learner_type]
, [attendance]

问题根因 #

现有代码的最后查询段将[attendance]、[driver_type]、[learner_type]三个分类维度都作为行维度返回，导致每个维度值组合单独占一行，同个分类的计数值被拆分到多行，必须手动累加才能得到总数。

实现方案 #

推荐方案：条件聚合（适配多维度转列，易维护） #

多维度同时转列时，条件聚合写法比嵌套PIVOT更简洁、性能更好，直接替换原有最后查询#Final的语句即可：

SELECT 
    [YearMonth],
    [Car],
    -- 出勤类型独立列
    SUM(CASE WHEN [attendance] = 'Attended' THEN [TotalLessons] ELSE 0 END) AS [Attended],
    SUM(CASE WHEN [attendance] = 'Cancelled' THEN [TotalLessons] ELSE 0 END) AS [Cancelled],
    SUM(CASE WHEN [attendance] = 'No Show' THEN [TotalLessons] ELSE 0 END) AS [NoShow],
    -- 司机类型独立列
    SUM(CASE WHEN [driver_type] = 'Learning' THEN [TotalLessons] ELSE 0 END) AS [Learning],
    SUM(CASE WHEN [driver_type] = 'Matured' THEN [TotalLessons] ELSE 0 END) AS [Matured],
    SUM(CASE WHEN [driver_type] = 'First' THEN [TotalLessons] ELSE 0 END) AS [First],
    SUM(CASE WHEN [driver_type] = 'Experienced' THEN [TotalLessons] ELSE 0 END) AS [Experienced],
    -- 学员类型独立列
    SUM(CASE WHEN [learner_type] = 'New' THEN [TotalLessons] ELSE 0 END) AS [NewLearner],
    SUM(CASE WHEN [learner_type] = 'Current' THEN [TotalLessons] ELSE 0 END) AS [CurrentLearner],
    -- 可选：所有分类总计数
    SUM([TotalLessons]) AS TotalAllLessons
FROM #Final
GROUP BY [YearMonth], [Car]
ORDER BY [Car], [YearMonth] DESC

原生PIVOT写法（单维度转列场景） #

如果只需要单个维度转列（例如仅将出勤类型转为独立列），可以使用原生PIVOT语法，结构如下：

SELECT 
    [YearMonth],
    [Car],
    [Attended],
    [Cancelled],
    [No Show]
FROM (
    -- 子查询只保留需要的行维度、转列维度、聚合值
    SELECT [YearMonth], [Car], [attendance], [TotalLessons]
    FROM #Final
) src
PIVOT (
    -- 聚合函数
    SUM([TotalLessons])
    -- 指定转列维度和需要生成的列值
    FOR [attendance] IN ([Attended], [Cancelled], [No Show])
) pvt
ORDER BY [Car], [YearMonth] DESC

SSRS场景优化建议 #

由于报表使用多参数动态筛选，不需要在SQL层硬编码所有分类列，直接在SSRS设计器中使用矩阵（Matrix）控件即可实现更灵活的动态列效果：

• 行组绑定：YearMonth、Car
• 列组绑定：对应分类维度（出勤类型/司机类型/学员类型）
• 值绑定：Sum(TotalLessons)
  控件会自动根据筛选结果动态生成对应列，不需要修改SQL逻辑。如果必须在SQL层实现动态列PIVOT，可以通过拼接动态SQL的方式，根据传入的多参数值生成PIVOT的IN子句，避免硬编码分类值。

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