如何使用SQL PIVOT实现错误码按日期的列转行计数？

方案一：使用PIVOT函数 #

首先需要统计每个日期下各错误码的出现次数，再通过PIVOT将错误码转为列。

固定错误码场景 #

如果错误码是已知固定的（比如EC001、EC002、EC003），可以用静态PIVOT语句：

SELECT [Date], [EC001], [EC002], [EC003]
FROM (
    -- 先聚合日期和错误码的出现次数
    SELECT 
        CAST(GETDATE() AS DATE) AS [Date], -- 转成DATE类型，避免时间部分干扰分组
        ErrorCode,
        COUNT(*) AS ErrorCount
    FROM Application
    GROUP BY CAST(GETDATE() AS DATE), ErrorCode
) AS SourceData
PIVOT (
    SUM(ErrorCount) -- 聚合后的次数用SUM/MAX效果一致，因为每组仅一条统计记录
    FOR ErrorCode IN ([EC001], [EC002], [EC003]) -- 指定要转为列的错误码
) AS PivotTable;

动态错误码场景 #

如果错误码不固定，需要用动态SQL自动获取所有错误码并构建PIVOT语句：

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

-- 拼接所有不同错误码为带引号的列名
SELECT @Columns = STRING_AGG(QUOTENAME(ErrorCode), ', ')
FROM (SELECT DISTINCT ErrorCode FROM Application) AS ErrorCodes;

-- 构建动态PIVOT查询
SET @SQL = N'
SELECT [Date], ' + @Columns + N'
FROM (
    SELECT 
        CAST(GETDATE() AS DATE) AS [Date],
        ErrorCode,
        COUNT(*) AS ErrorCount
    FROM Application
    GROUP BY CAST(GETDATE() AS DATE), ErrorCode
) AS SourceData
PIVOT (
    SUM(ErrorCount)
    FOR ErrorCode IN (' + @Columns + N')
) AS PivotTable;';

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

方案二：条件聚合（无需PIVOT） #

不想用PIVOT的话，条件聚合是更灵活的替代方案，适合错误码数量不多的场景：

SELECT 
    CAST(GETDATE() AS DATE) AS [Date],
    -- 每个错误码对应一个条件计数
    COUNT(CASE WHEN ErrorCode = 'EC001' THEN 1 END) AS [EC001],
    COUNT(CASE WHEN ErrorCode = 'EC002' THEN 1 END) AS [EC002],
    COUNT(CASE WHEN ErrorCode = 'EC003' THEN 1 END) AS [EC003]
FROM Application
GROUP BY CAST(GETDATE() AS DATE);

注意事项 #

• 用CAST(GETDATE() AS DATE)是为了去除时间部分，确保同一日期的记录被正确分组；如果表中存储的是历史日期，替换为表中的日期字段即可，比如CAST(LogDate AS DATE) AS [Date]。

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