SQL中如何将三类数据合并为单个Dynamic Pivot多列透视查询？

核心思路 #

先将原表中同一属性的日期、布尔、代码三类值，转换为带后缀的统一键值对格式，再通过动态透视一次性生成目标列结构，无需多次关联。

具体实现步骤（以SQL Server为例） #

1. 预处理原数据，生成带后缀的属性标识

假设你的原始数据表结构为：

CREATE TABLE YourTable (
    Row_ID INT,
    Attribute_Name VARCHAR(100), -- 示例值：'Addendum Included'
    Date_Value DATE,
    Bool_Value BIT,
    Code_Value VARCHAR(50)
)

先通过UNPIVOT将三类值拆分为统一的行记录，同时拼接出目标格式的列名：

SELECT 
    Row_ID,
    CONCAT(Attribute_Name, '_', Value_Type) AS Pivot_Column,
    Value_Data
FROM (
    SELECT 
        Row_ID,
        Attribute_Name,
        CAST(Date_Value AS VARCHAR(50)) AS Date_Value, -- 统一转字符串避免类型冲突
        CAST(Bool_Value AS VARCHAR(5)) AS Bool_Value,
        Code_Value
    FROM YourTable
) t
UNPIVOT (
    Value_Data FOR Value_Type IN (Date_Value, Bool_Value, Code_Value)
) up

这一步会把单条记录拆分为3行，分别对应Addendum Included_Date、Addendum Included_Bool、Addendum Included_Code的键值对。

2. 动态生成透视列列表

自动收集所有需要透视的列名，拼接成SQL可识别的格式：

DECLARE @PivotColumns NVARCHAR(MAX)
SELECT @PivotColumns = STRING_AGG(QUOTENAME(Pivot_Column), ', ')
FROM (
    SELECT DISTINCT CONCAT(Attribute_Name, '_', Value_Type) AS Pivot_Column
    FROM YourTable
    CROSS JOIN (VALUES ('Date'), ('Bool'), ('Code')) vt(Value_Type)
) t

如果使用SQL Server 2016及更早版本，替换STRING_AGG为FOR XML PATH的拼接方式即可。

3. 执行动态透视SQL

将预处理逻辑和透视逻辑组合成动态SQL执行：

DECLARE @DynamicSQL NVARCHAR(MAX)
SET @DynamicSQL = N'
SELECT Row_ID, ' + @PivotColumns + N'
FROM (
    SELECT 
        Row_ID,
        CONCAT(Attribute_Name, ''_'', Value_Type) AS Pivot_Column,
        Value_Data
    FROM (
        SELECT 
            Row_ID,
            Attribute_Name,
            CAST(Date_Value AS VARCHAR(50)) AS Date_Value,
            CAST(Bool_Value AS VARCHAR(5)) AS Bool_Value,
            Code_Value
        FROM YourTable
    ) t
    UNPIVOT (
        Value_Data FOR Value_Type IN (Date_Value, Bool_Value, Code_Value)
    ) up
) src
PIVOT (
    MAX(Value_Data) FOR Pivot_Column IN (' + @PivotColumns + N')
) pvt
'
EXEC sp_executesql @DynamicSQL

关键细节 #

• 统一转字符串是因为透视要求聚合列类型一致，后续可通过CAST/CONVERT将结果还原为原数据类型（比如把Addendum Included_Bool转回BIT类型）
• 若使用MySQL、PostgreSQL等其他数据库，只需替换对应函数：比如STRING_AGG换成GROUP_CONCAT，UNPIVOT换成UNION ALL手动拆分，动态执行用PREPARE/EXECUTE

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