SQL Server动态Pivot按日期分组统计ResultCode及每日总数

原代码问题分析 #

1. 字段不匹配：子查询SELECT的是CAST(Date as date)，但GROUP BY用了StartTime，二者必须统一。
2. 缺少关键字段：PIVOT需要基于ResultCode转列，但原查询子查询未包含该字段，反而错误使用聚合后的Result字段。
3. PIVOT语法错误：FOR Result IN ([ResultCode])写法无效，IN子句必须指定具体的ResultCode值，动态场景需自动生成列列表。
4. 筛选条件错误：原代码WHERE条件写的OperationType = 'Begin'与数据中的BeginTransaction不匹配，会导致无数据返回。

静态PIVOT（ResultCode固定时使用） #

如果已知所有ResultCode值，可直接写死列名：

SELECT 
    [Day],
    ISNULL([-3], 0) AS [-3],
    ISNULL([-5], 0) AS [-5],
    ISNULL([-10], 0) AS [-10],
    ISNULL([-28], 0) AS [-28],
    ISNULL([-30], 0) AS [-30],
    Total
FROM (
    SELECT 
        CAST(Date AS DATE) AS [Day],
        ResultCode,
        COUNT(*) OVER (PARTITION BY CAST(Date AS DATE)) AS Total,
        COUNT(*) AS CountPerCode
    FROM OperationLogs
    WHERE OperationType = 'BeginTransaction'
    GROUP BY CAST(Date AS DATE), ResultCode
) AS Source
PIVOT (
    SUM(CountPerCode)
    FOR ResultCode IN ([ -3 ], [ -5 ], [ -10 ], [ -28 ], [ -30 ])
) AS PivotTable
ORDER BY [Day];

动态PIVOT（ResultCode不固定时使用，适配SQL Server 13.0/2016） #

由于你提到ResultCode数量多且不固定，需要动态生成列，以下是适配SQL Server 2016的实现：

DECLARE @Columns NVARCHAR(MAX), @SQL NVARCHAR(MAX);

-- 拼接所有唯一ResultCode为带方括号的列名
SELECT @Columns = STUFF(
    (SELECT ', ' + QUOTENAME(ResultCode)
     FROM (SELECT DISTINCT ResultCode FROM OperationLogs WHERE OperationType = 'BeginTransaction') AS Codes
     ORDER BY ResultCode
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 2, ''
);

-- 拼接完整动态SQL语句
SET @SQL = N'
SELECT 
    [Day],
    ' + @Columns + ',
    Total
FROM (
    SELECT 
        CAST(Date AS DATE) AS [Day],
        ResultCode,
        COUNT(*) OVER (PARTITION BY CAST(Date AS DATE)) AS Total,
        COUNT(*) AS CountPerCode
    FROM OperationLogs
    WHERE OperationType = ''BeginTransaction''
    GROUP BY CAST(Date AS DATE), ResultCode
) AS Source
PIVOT (
    SUM(CountPerCode)
    FOR ResultCode IN (' + @Columns + ')
) AS PivotTable
ORDER BY [Day];';

-- 执行动态SQL
EXEC sp_executesql @SQL;

关键说明 #

• 用ISNULL处理无数据的ResultCode，确保显示0而非NULL。
• 通过COUNT(*) OVER (PARTITION BY CAST(Date AS DATE))计算每日总数，无需额外聚合。
• 动态SQL中用STUFF + FOR XML PATH拼接列名，适配SQL Server 2016及更早版本（如果是2017+可改用STRING_AGG简化）。

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