如何用SELECT查询将带UniqueId的多行键值对数据转置为宽表？

需求说明 #

需要把存储为键值对格式的表（包含UniqueId、ColName、ColValue字段），转换为以UniqueId为唯一标识，ColName作为列名的宽表结构。

原始数据结构 #

UniqueId    ColName    ColValue    UpdateTimestamp
123         Col1       ValueA      2023-08-15T11:25:14-01:00
123         Col2       ValueB      2023-08-15T11:25:14-01:00
123         Col3       ValueC      2023-08-15T11:25:14-01:00
456         Col1       ValueD      2023-08-15T11:25:14-01:00
456         Col2       ValueE      2023-08-15T11:25:14-01:00
456         Col3       ValueF      2023-08-15T11:25:14-01:00
789         Col1       ValueG      2023-08-15T11:25:14-01:00
789         Col2       ValueH      2023-08-15T11:25:14-01:00
789         Col3       ValueI      2023-08-15T11:25:14-01:00

目标宽表结构 #

UniqueId    Col1    Col2    Col3
123         ValueA  ValueB  ValueC
456         ValueD  ValueE  ValueF
789         ValueG  ValueH  ValueI

实现方法 #

通用CASE WHEN写法（支持所有主流数据库） #

这种写法不依赖数据库特定语法，兼容性最强：

SELECT
    UniqueId,
    MAX(CASE WHEN ColName = 'Col1' THEN ColValue END) AS Col1,
    MAX(CASE WHEN ColName = 'Col2' THEN ColValue END) AS Col2,
    MAX(CASE WHEN ColName = 'Col3' THEN ColValue END) AS Col3
FROM
    your_table_name  -- 替换为你的表名
GROUP BY
    UniqueId;

说明：通过CASE WHEN匹配对应的列名取出值，再用GROUP BY UniqueId合并同一标识的行，MAX/MIN聚合函数不影响结果（因为每个UniqueId+ColName只有一条数据）。

SQL Server/Power BI 专用PIVOT写法 #

如果使用支持PIVOT语法的数据库，可以用更简洁的写法：

SELECT
    UniqueId,
    Col1,
    Col2,
    Col3
FROM
    your_table_name
PIVOT
(
    MAX(ColValue)
    FOR ColName IN (Col1, Col2, Col3)
) AS PivotTable;

PostgreSQL 专用写法（需tablefunc扩展） #

PostgreSQL需要先启用tablefunc扩展，再使用crosstab函数：

-- 先启用扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT
    *
FROM
    crosstab(
        'SELECT UniqueId, ColName, ColValue FROM your_table_name ORDER BY 1,2',
        'SELECT unnest(''{Col1,Col2,Col3}''::text[])'
    ) AS ct(UniqueId INT, Col1 TEXT, Col2 TEXT, Col3 TEXT);

Oracle 专用PIVOT写法 #

Oracle的PIVOT语法需要指定列名的字符串形式：

SELECT
    UniqueId,
    Col1,
    Col2,
    Col3
FROM
    your_table_name
PIVOT
(
    MAX(ColValue)
    FOR ColName IN ('Col1' AS Col1, 'Col2' AS Col2, 'Col3' AS Col3)
)
ORDER BY UniqueId;

进阶：处理多版本数据 #

如果同一UniqueId+ColName存在多条更新记录，需要取最新版本的话，可以先筛选出最新数据再转换：

WITH latest_data AS (
    SELECT
        UniqueId,
        ColName,
        ColValue,
        -- 按更新时间倒序排序，取第一条
        ROW_NUMBER() OVER (PARTITION BY UniqueId, ColName ORDER BY UpdateTimestamp DESC) AS rn
    FROM
        your_table_name
)
SELECT
    UniqueId,
    MAX(CASE WHEN ColName = 'Col1' THEN ColValue END) AS Col1,
    MAX(CASE WHEN ColName = 'Col2' THEN ColValue END) AS Col2,
    MAX(CASE WHEN ColName = 'Col3' THEN ColValue END) AS Col3
FROM
    latest_data
WHERE
    rn = 1  -- 只保留最新版本
GROUP BY
    UniqueId;

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