SQL双动态列PIVOT实现需求求助

我懂你在Stack Overflow翻了好多双列PIVOT的帖子，但都没找到贴合你需求的方案——别担心，我这就给你量身定制转换方案，还有完整的初始化建表及插入数据脚本。

一、完整初始化脚本 #

先给你补全并整理好建表和插入数据的SQL，方便你直接测试：

CREATE TABLE TblPivot (
    ID INT IDENTITY(1, 1),
    Shop VARCHAR(MAX),
    ElementId VARCHAR(10),
    QuestionId VARCHAR(10),
    [Value] VARCHAR(20)
);
GO

-- 插入示例数据（补全你未写完的部分）
INSERT INTO TblPivot (Shop,ElementId,QuestionId,[Value]) 
VALUES ('Shop1','elem10','question1','one');
GO

INSERT INTO TblPivot (Shop,ElementId,QuestionId,[Value]) 
VALUES ('Shop1','elem10','question2','two');
GO

INSERT INTO TblPivot (Shop,ElementId,QuestionId,[Value]) 
VALUES ('Shop1','elem20','question1','three');
GO

INSERT INTO TblPivot (Shop,ElementId,QuestionId,[Value]) 
VALUES ('Shop2','elem10','question1','four');
GO

INSERT INTO TblPivot (Shop,ElementId,QuestionId,[Value]) 
VALUES ('Shop2','elem20','question2','five');
GO

二、目标转换样式说明 #

假设你想要的是把ElementId和QuestionId组合作为列名，以Shop为行维度，对应的Value填充到交叉单元格，最终效果如下：

三、双列PIVOT实现方案 #

SQL Server的原生PIVOT只支持单个列作为透视维度，所以我们需要先把ElementId和QuestionId拼接成一个复合列名，再执行透视操作，这里分两种场景：

1. 静态PIVOT（已知所有组合） #

如果你的ElementId和QuestionId组合是固定不变的，用静态PIVOT性能更好：

SELECT 
    Shop,
    [elem10_question1],
    [elem10_question2],
    [elem20_question1],
    [elem20_question2]
FROM (
    SELECT 
        Shop,
        -- 拼接复合列名
        CONCAT(ElementId, '_', QuestionId) AS ColumnName,
        [Value]
    FROM TblPivot
) AS SourceTable
PIVOT (
    -- 因为每个Shop+组合列唯一，用MAX/MIN都不影响结果
    MAX([Value])
    FOR ColumnName IN (
        [elem10_question1],
        [elem10_question2],
        [elem20_question1],
        [elem20_question2]
    )
) AS PivotTable;

2. 动态PIVOT（组合动态变化） #

如果ElementId和QuestionId的组合会随时新增或修改，用动态SQL自动生成列名更灵活：

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

-- 自动生成所有唯一的复合列名
SELECT @Columns = STRING_AGG(QUOTENAME(CONCAT(ElementId, '_', QuestionId)), ', ')
FROM (SELECT DISTINCT ElementId, QuestionId FROM TblPivot) AS UniqueCombos;

-- 构建动态透视查询
SET @Query = N'
SELECT Shop, ' + @Columns + '
FROM (
    SELECT 
        Shop,
        CONCAT(ElementId, ''_'', QuestionId) AS ColumnName,
        [Value]
    FROM TblPivot
) AS SourceTable
PIVOT (
    MAX([Value])
    FOR ColumnName IN (' + @Columns + ')
) AS PivotTable;';

-- 执行动态查询
EXEC sp_executesql @Query;

小提示 #

• 如果同一个Shop+ElementId+QuestionId组合有多条数据，你可以把MAX([Value])换成STRING_AGG([Value], ', ')（SQL Server 2017及以上版本）来合并所有值，或者根据业务需求选择其他聚合函数。
• 静态PIVOT适合组合固定的场景，执行效率更高；动态PIVOT则适配组合频繁变化的情况。

内容的提问来源于stack exchange，提问作者Cătălin Rădoi