SQL实现类透视表转换：将列值映射为新列

原始数据表 #

Id flag_0 doc date
1  1      1   d1
1  1      2   d2
1  1      3   d3
2  0      1   d4
2  0      2   d5

目标透视表 #

Id flag_0 doc_1 doc_2 doc_3
1  1      d1     d2   d3 
2  0      d4     d5   NULL

通用解法：条件聚合（适配多数SQL数据库） #

这是最通用的实现方式，不依赖数据库特定函数，适用于MySQL、SQL Server、PostgreSQL、Oracle等：

SELECT 
    Id,
    flag_0,
    MAX(CASE WHEN doc = 1 THEN date END) AS doc_1,
    MAX(CASE WHEN doc = 2 THEN date END) AS doc_2,
    MAX(CASE WHEN doc = 3 THEN date END) AS doc_3
FROM 
    你的表名 -- 替换为实际表名
GROUP BY 
    Id, flag_0;

说明：利用CASE语句匹配doc值，通过MAX（或MIN，因每组仅一条有效数据）聚合，不匹配的行返回NULL，对应需求中的缺失值。

数据库专用解法 #

MySQL 可选实现 #

SELECT 
    Id,
    flag_0,
    SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(IF(doc=1, date, NULL) SEPARATOR '|'), '|', 1), '|', -1) AS doc_1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(IF(doc=2, date, NULL) SEPARATOR '|'), '|', 1), '|', -1) AS doc_2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(IF(doc=3, date, NULL) SEPARATOR '|'), '|', 1), '|', -1) AS doc_3
FROM 
    你的表名
GROUP BY 
    Id, flag_0;

注：若date包含分隔符（示例用|），需替换为无冲突字符。

SQL Server 专用（PIVOT函数） #

SELECT 
    Id, flag_0, [1] AS doc_1, [2] AS doc_2, [3] AS doc_3
FROM 
    (SELECT Id, flag_0, doc, date FROM 你的表名) AS SourceTable
PIVOT 
    (MAX(date) FOR doc IN ([1], [2], [3])) AS PivotTable;

PostgreSQL 专用（crosstab函数） #

需先启用tablefunc扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

再执行透视查询：

SELECT 
    *
FROM 
    crosstab(
        'SELECT Id, flag_0, doc, date FROM 你的表名 ORDER BY 1,2,3',
        'SELECT unnest(ARRAY[1,2,3])'
    ) AS ct(Id INT, flag_0 INT, doc_1 TEXT, doc_2 TEXT, doc_3 TEXT);

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