SQL查询求助：如何将同一客户的多行数据转为多列？

针对你需要将同一customer_id的多行订单合并为单行、新增列存储额外item和amount的需求，这里提供两种可行方案，适配最多5行数据的场景：

方案一：条件聚合（跨数据库通用） #

这是最简洁且通用的实现方式，利用窗口函数给每个客户的订单编号，再通过条件判断将行数据转成列：

步骤1：给每个客户的订单分配行号 #

先通过ROW_NUMBER()窗口函数，按customer_id分组、order_id排序，为每行数据生成唯一行号：

SELECT 
    order_id,
    item,
    amount,
    customer_id,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_id) AS row_num
FROM your_table_name;

步骤2：聚合转列 #

基于上面的子查询，用MAX()配合CASE语句，将不同行号的item和amount转成对应列：

SELECT 
    MAX(CASE WHEN row_num = 1 THEN order_id END) AS order_id,
    MAX(CASE WHEN row_num = 1 THEN item END) AS item,
    MAX(CASE WHEN row_num = 1 THEN amount END) AS amount,
    customer_id,
    MAX(CASE WHEN row_num = 2 THEN item END) AS item_2,
    MAX(CASE WHEN row_num = 2 THEN amount END) AS amount_2,
    MAX(CASE WHEN row_num = 3 THEN item END) AS item_3,
    MAX(CASE WHEN row_num = 3 THEN amount END) AS amount_3,
    MAX(CASE WHEN row_num = 4 THEN item END) AS item_4,
    MAX(CASE WHEN row_num = 4 THEN amount END) AS amount_4,
    MAX(CASE WHEN row_num = 5 THEN item END) AS item_5,
    MAX(CASE WHEN row_num = 5 THEN amount END) AS amount_5
FROM (
    SELECT 
        order_id,
        item,
        amount,
        customer_id,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_id) AS row_num
    FROM your_table_name
) AS numbered_orders
GROUP BY customer_id
ORDER BY order_id;

说明：

• 每个row_num在同一customer_id组内唯一，MAX()仅用于提取对应行的值，无匹配时返回NULL（即空值）
• 可根据需求调整ORDER BY order_id的排序逻辑，比如按订单创建时间排序

方案二：自连接（适合少量行扩展） #

如果你的数据库对窗口函数支持有限，可通过多次左连接实现：

SELECT 
    o1.order_id,
    o1.item,
    o1.amount,
    o1.customer_id,
    o2.item AS item_2,
    o2.amount AS amount_2,
    o3.item AS item_3,
    o3.amount AS amount_3,
    o4.item AS item_4,
    o4.amount AS amount_4,
    o5.item AS item_5,
    o5.amount AS amount_5
FROM (
    SELECT *
    FROM your_table_name
    WHERE ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_id) = 1
) o1
LEFT JOIN (
    SELECT *
    FROM your_table_name
    WHERE ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_id) = 2
) o2 ON o1.customer_id = o2.customer_id
LEFT JOIN (
    SELECT *
    FROM your_table_name
    WHERE ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_id) = 3
) o3 ON o1.customer_id = o3.customer_id
LEFT JOIN (
    SELECT *
    FROM your_table_name
    WHERE ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_id) = 4
) o4 ON o1.customer_id = o4.customer_id
LEFT JOIN (
    SELECT *
    FROM your_table_name
    WHERE ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_id) = 5
) o5 ON o1.customer_id = o5.customer_id
ORDER BY o1.order_id;

说明：

• 每次连接提取对应行号的订单数据，左连接确保即使客户没有对应行也能保留主行数据
• 缺点是行数越多，连接次数越多，代码冗余度越高

注意事项 #

• 替换SQL中的your_table_name为实际表名
• 若使用MySQL 5.x及以下版本，不支持ROW_NUMBER()，需用用户变量模拟行号分配
• 部分数据库（如SQL Server、Oracle）支持PIVOT语法，但条件聚合的跨数据库兼容性更好

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