按类型将表数据分组至不同列的SQL实现方法

我有一张包含id和type两列的表，数据如下：

希望通过SELECT语句将数据按type分组，分别放入account_id_column、client_id_column和order_id_column三个列中，得到如下结果：

不清楚具体实现方式，是否需要使用JOIN结合WHERE子句与GROUP BY？

不需要复杂的多表JOIN，你可以通过窗口函数生成分组内的行号，再结合条件聚合来实现这个行转列的需求，步骤如下：

1. 给每个类型的记录添加行号 #

先用ROW_NUMBER()窗口函数，按type分组，给每个分组内的记录分配递增行号，这样同序号的行就对应结果里的同一行：

SELECT 
    id,
    type,
    ROW_NUMBER() OVER (PARTITION BY type ORDER BY id) AS row_num
FROM your_table_name;

执行后得到中间结果：

2. 按行号聚合生成目标列 #

基于上述中间结果，通过GROUP BY row_num分组，再用CASE WHEN提取对应类型的id作为目标列：

SELECT
    MAX(CASE WHEN type = 'ACCOUNT' THEN id END) AS account_id_column,
    MAX(CASE WHEN type = 'CLIENT' THEN id END) AS client_id_column,
    MAX(CASE WHEN type = 'ORDER' THEN id END) AS order_id_column
FROM (
    SELECT 
        id,
        type,
        ROW_NUMBER() OVER (PARTITION BY type ORDER BY id) AS row_num
    FROM your_table_name
) AS numbered_rows
GROUP BY row_num
ORDER BY row_num;

为什么用MAX()？ #

每个row_num分组下，每个type只会有一条有效记录，MAX()（或MIN()）可以过滤掉非目标类型的NULL值，留下对应的id。

替代方案：使用PIVOT（部分数据库支持） #

如果你的数据库支持PIVOT语法（如SQL Server、Oracle），可以更简洁实现：

SELECT 
    ACCOUNT AS account_id_column,
    CLIENT AS client_id_column,
    "ORDER" AS order_id_column
FROM (
    SELECT 
        id,
        type,
        ROW_NUMBER() OVER (PARTITION BY type ORDER BY id) AS row_num
    FROM your_table_name
) AS numbered_rows
PIVOT (
    MAX(id)
    FOR type IN (ACCOUNT, CLIENT, "ORDER")
) AS pivoted_table
ORDER BY row_num;

注：ORDER是SQL关键字，部分数据库需要用双引号包裹避免语法错误。

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