在MS SQL Server中通过两次PIVOT实现数据扁平化与分组

场景1：基于第一次PIVOT后的结果二次扁平化 #

假设你第一次PIVOT后得到的中间表结构如下（每行对应建筑的一组高度和面积）：

可以通过UNION ALL将每组的Height和Area拆分为键值对，拼接组号生成唯一列标识，最后用PIVOT转成单行列结构：

WITH PivotedOnce AS (
    -- 替换成你第一次PIVOT得到的表/查询语句
    SELECT 
        BuildingID,
        Height,
        Area,
        GroupNumber
    FROM YourFirstPivotResult
),
Unpivoted AS (
    -- 将Height和Area拆分为独立键值对，拼接组号作为列标识
    SELECT 
        BuildingID,
        CONCAT('Height_', GroupNumber) AS PivotColumn,
        CAST(Height AS VARCHAR(50)) AS AttributeValue -- 统一数据类型避免PIVOT报错
    FROM PivotedOnce
    UNION ALL
    SELECT 
        BuildingID,
        CONCAT('Area_', GroupNumber) AS PivotColumn,
        CAST(Area AS VARCHAR(50)) AS AttributeValue
    FROM PivotedOnce
)
SELECT 
    BuildingID,
    [Height_1], [Area_1], [Height_2], [Area_2] -- 根据实际分组数量调整列名
FROM Unpivoted
PIVOT (
    MAX(AttributeValue) -- 用MAX/MIN均可，每个PivotColumn对应唯一值
    FOR PivotColumn IN ([Height_1], [Area_1], [Height_2], [Area_2])
) AS FinalPivoted;

场景2：直接从原始明细数据完成两次扁平化 #

如果你的原始数据是明细格式（每行对应建筑的一个属性值），比如：

先给每个建筑的属性组分配序号，再执行键值对拆分+PIVOT流程：

WITH RankedData AS (
    -- 给每个建筑的属性组分配序号，确保同一组的Height/Area对应相同GroupNum
    SELECT 
        BuildingID,
        AttributeName,
        AttributeValue,
        ROW_NUMBER() OVER (PARTITION BY BuildingID, AttributeName ORDER BY (SELECT NULL)) AS GroupNum
    FROM YourOriginalTable
),
Unpivoted AS (
    SELECT 
        BuildingID,
        CONCAT(AttributeName, '_', GroupNum) AS PivotColumn,
        CAST(AttributeValue AS VARCHAR(50)) AS AttributeValue
    FROM RankedData
)
SELECT 
    BuildingID,
    [Height_1], [Area_1], [Height_2], [Area_2]
FROM Unpivoted
PIVOT (
    MAX(AttributeValue)
    FOR PivotColumn IN ([Height_1], [Area_1], [Height_2], [Area_2])
) AS FinalPivoted;

处理动态分组数量 #

如果建筑的分组数量不固定（比如有的建筑有2组，有的有3组），硬编码列名会遗漏数据，这时可以用动态SQL自动生成PIVOT的列列表：

DECLARE @PivotColumns NVARCHAR(MAX);

WITH PivotedOnce AS (
    SELECT 
        BuildingID,
        Height,
        Area,
        GroupNumber
    FROM YourFirstPivotResult
),
ColumnList AS (
    SELECT CONCAT('Height_', GroupNumber) AS PivotColumn FROM PivotedOnce
    UNION
    SELECT CONCAT('Area_', GroupNumber) AS PivotColumn FROM PivotedOnce
)
SELECT @PivotColumns = STRING_AGG(QUOTENAME(PivotColumn), ', ') FROM ColumnList;

DECLARE @SQL NVARCHAR(MAX) = N'
WITH PivotedOnce AS (
    SELECT 
        BuildingID,
        Height,
        Area,
        GroupNumber
    FROM YourFirstPivotResult
),
Unpivoted AS (
    SELECT 
        BuildingID,
        CONCAT(''Height_'', GroupNumber) AS PivotColumn,
        CAST(Height AS VARCHAR(50)) AS AttributeValue
    FROM PivotedOnce
    UNION ALL
    SELECT 
        BuildingID,
        CONCAT(''Area_'', GroupNumber) AS PivotColumn,
        CAST(Area AS VARCHAR(50)) AS AttributeValue
    FROM PivotedOnce
)
SELECT 
    BuildingID,
    ' + @PivotColumns + '
FROM Unpivoted
PIVOT (
    MAX(AttributeValue)
    FOR PivotColumn IN (' + @PivotColumns + ')
) AS FinalPivoted;';

EXEC sp_executesql @SQL;

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