如何用SQL实现Pandas crosstab功能？动态透视表遇空表问题

问题背景 #

你想要在SQL中实现类似Pandas crosstab的功能，基于temp2表生成用户×Code的透视表，其中缺失的Code值显示为0。现有代码返回空表，且需要处理数百个Code值，无法手动列举。

temp2表结构及样本数据：

目标输出：

现有代码的关键错误 #

你的代码有几个核心问题导致返回空表：

1. @ColumnName赋值错误：你错误地收集了User列的值，而不是需要透视的Code列的唯一值。
2. PIVOT语法错误：sum(Used) as sum是无效语法，PIVOT中聚合函数直接写SUM(Used)即可。
3. 未处理NULL的Code值：SQL无法直接将NULL作为列名，需要将其转换为合法的字符串标识。
4. 未处理缺失值为0：透视后没有对应记录的单元格会显示NULL，需要转换为0。

修正后的解决方案 #

以下是适配SQL Server的动态透视表代码，分两种版本（适配不同SQL Server版本）：

版本1：SQL Server 2017+（支持STRING_AGG）

DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @SelectColumns AS NVARCHAR(MAX)
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)

-- 1. 收集所有唯一的Code值，将NULL转换为合法列名并包裹方括号
SELECT @ColumnName = STRING_AGG(QUOTENAME(ISNULL(Code, '[Null_Code]')), ', ')
FROM (SELECT DISTINCT Code FROM temp2) AS Codes

-- 2. 构建SELECT列表，用COALESCE将NULL转为0
SELECT @SelectColumns = STRING_AGG('COALESCE(' + QUOTENAME(ISNULL(Code, '[Null_Code]')) + ', 0) AS ' + QUOTENAME(ISNULL(Code, '[Null_Code]')), ', ')
FROM (SELECT DISTINCT Code FROM temp2) AS Codes

-- 3. 构建动态透视查询
SET @DynamicPivotQuery = N'
SELECT 
    [User],
    ' + @SelectColumns + '
FROM (
    SELECT 
        [User],
        -- 将源数据中的NULL Code转换为统一标识
        ISNULL(Code, ''[Null_Code]'') AS Code,
        Used
    FROM temp2
) AS src
PIVOT (
    SUM(Used) -- 聚合函数，这里用SUM符合你的需求
    FOR Code IN (' + @ColumnName + ')
) AS piv
ORDER BY [User]'

-- 执行动态查询
EXEC sp_executesql @DynamicPivotQuery

版本2：SQL Server 2016及更早版本（使用FOR XML PATH拼接字符串）

DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @SelectColumns AS NVARCHAR(MAX)
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)

-- 1. 收集唯一Code值，处理NULL并拼接列名
SELECT @ColumnName = STUFF((
    SELECT ', ' + QUOTENAME(ISNULL(Code, '[Null_Code]'))
    FROM (SELECT DISTINCT Code FROM temp2) AS Codes
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

-- 2. 构建带COALESCE的SELECT列表
SELECT @SelectColumns = STUFF((
    SELECT ', COALESCE(' + QUOTENAME(ISNULL(Code, '[Null_Code]')) + ', 0) AS ' + QUOTENAME(ISNULL(Code, '[Null_Code]'))
    FROM (SELECT DISTINCT Code FROM temp2) AS Codes
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

-- 3. 构建动态查询
SET @DynamicPivotQuery = N'
SELECT 
    [User],
    ' + @SelectColumns + '
FROM (
    SELECT 
        [User],
        ISNULL(Code, ''[Null_Code]'') AS Code,
        Used
    FROM temp2
) AS src
PIVOT (
    SUM(Used)
    FOR Code IN (' + @ColumnName + ')
) AS piv
ORDER BY [User]'

EXEC sp_executesql @DynamicPivotQuery

关键说明 #

• 处理NULL Code：我们将源数据中的NULL Code转换为[Null_Code]作为列名，避免SQL语法错误，你也可以改成更贴合业务的名称（比如[Empty_Code]）。
• 保留0值：通过COALESCE函数将透视后无数据的NULL值转换为0，满足你的需求。
• 动态列生成：自动收集所有唯一的Code值，无需手动列举，适配数百个Code的场景。

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