MySQL动态行转列：将snapdate转为列展示对应createdate值

通用解决方案（条件聚合） #

这种方法适配所有主流SQL数据库（MySQL、PostgreSQL、SQL Server、Oracle等），通过CASE语句配合聚合函数实现转置：

SELECT
    ID,
    MAX(CASE WHEN snapdate = '2022-10-03' THEN createdate END) AS `createdate(for 2022-10-03)`,
    MAX(CASE WHEN snapdate = '2022-10-10' THEN createdate END) AS `createdate(for 2022-10-10)`
FROM
    your_table_name
GROUP BY
    ID;

说明 #

• 替换your_table_name为你的实际表名
• 每个snapdate值对应一条CASE语句，用MAX（或MIN，因每个ID+snapdate组合仅对应一条数据）提取对应createdate
• 列名用反引号（`）包裹，规避空格或特殊字符引发的语法错误

特定数据库的PIVOT语法 #

若你的数据库支持PIVOT语法，可使用更简洁的写法：

SQL Server #

SELECT
    ID,
    [2022-10-03] AS `createdate(for 2022-10-03)`,
    [2022-10-10] AS `createdate(for 2022-10-10)`
FROM
    your_table_name
PIVOT (
    MAX(createdate)
    FOR snapdate IN ([2022-10-03], [2022-10-10])
) AS PivotTable;

MySQL 8.0+ #

SELECT
    ID,
    `2022-10-03` AS `createdate(for 2022-10-03)`,
    `2022-10-10` AS `createdate(for 2022-10-10)`
FROM
    your_table_name
PIVOT (
    MAX(createdate)
    FOR snapdate IN ('2022-10-03', '2022-10-10')
) AS PivotTable;

PostgreSQL #

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

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT
    ID,
    "2022-10-03" AS "createdate(for 2022-10-03)",
    "2022-10-10" AS "createdate(for 2022-10-10)"
FROM
    crosstab(
        'SELECT ID, snapdate, createdate FROM your_table_name ORDER BY 1,2',
        'VALUES (''2022-10-03''), (''2022-10-10'')'
    ) AS ct(ID INT, "2022-10-03" DATE, "2022-10-10" DATE);

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