SQL Server多值多行Pivot实现求助：未知列名动态转置

嘿，我看你在处理SQL Server的动态Pivot时遇到了麻烦，部门不固定确实是静态Pivot搞不定的场景，先帮你分析下当前代码的问题，再给你一个能完美适配动态部门的解决方案。

首先，你当前的Pivot写法有几个关键错误：

• PIVOT 里的 FOR department IN (...) 括号里应该填具体的部门名称，而不是department, employees这些列名；
• 静态Pivot没法处理不固定的部门列表，必须用动态SQL自动生成部门列。

假设你的原表etl_insight_counts结构是每个部门一行，包含department、employees（人数）、pct（占比）、up_down（升降状态）这几个字段，要得到目标图里那种「指标行+部门列」的转置结果，需要先把列转成行（Unpivot），再动态转成部门列（Pivot），具体代码如下：

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

-- 第一步：自动获取所有部门名称，拼接成Pivot需要的列格式（带方括号避免特殊字符）
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(department)
                    FROM etl_insight_counts
                    GROUP BY department
                    ORDER BY department
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

-- 第二步：构建动态查询，先Unpivot把指标列转成行，再Pivot转成部门列
SET @query = N'
WITH unpivoted AS (
    SELECT 
        department,
        metric_type,
        -- 把所有指标值转成字符串，避免类型不匹配报错
        CAST(metric_value AS NVARCHAR(100)) AS metric_value
    FROM (
        SELECT 
            department,
            CAST(employees AS NVARCHAR(100)) AS employees,
            CAST(pct AS NVARCHAR(100)) AS pct,
            up_down
        FROM etl_insight_counts
    ) AS src
    UNPIVOT (
        metric_value FOR metric_type IN (employees, pct, up_down)
    ) AS unpvt
)
SELECT 
    metric_type AS 指标,
    ' + @cols + N'
FROM unpivoted
PIVOT (
    MAX(metric_value) FOR department IN (' + @cols + N')
) AS piv;
';

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

代码解释： #

1. 动态生成部门列：用STUFF+XML PATH把所有部门名称拼接成[部门A],[部门B],...的格式，确保即使新增部门也能自动识别；
2. Unpivot转换：把原来的employees、pct、up_down列转成metric_type（指标类型）和metric_value（指标值）的行结构，这样每个部门的每个指标都是一行数据；
3. Pivot转置：把部门名称转成列，用MAX聚合函数（因为每个部门每个指标只有一个值，MAX不影响结果）提取对应的值。

注意事项： #

• 如果你的原表结构不是「一行一个部门」，而是「一行一个部门的一个指标」，可以调整Unpivot的部分，核心逻辑还是动态生成部门列；
• 转换为字符串是为了避免不同类型字段（比如数字、字符串）在Unpivot时的类型冲突，如果所有指标都是同类型，可以去掉CAST转换。

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