如何将销售表中同一客户的多行销售记录合并为单行展示

嘿，这个需求就是典型的**行转列（pivot）**场景嘛！根据你使用的数据库不同，实现方式略有差异，我给你整理了几个常用数据库的解决方案：

MySQL 实现 #

MySQL没有原生的PIVOT函数，咱们可以用条件聚合来实现这个需求：

SELECT 
    Customer,
    MAX(CASE WHEN rn = 1 THEN Product END) AS Product,
    MAX(CASE WHEN rn = 1 THEN Amount END) AS Amount,
    MAX(CASE WHEN rn = 2 THEN Product END) AS Product2,
    MAX(CASE WHEN rn = 2 THEN Amount END) AS Amount2
FROM (
    -- 先给每个客户的销售记录按产品排序并编号
    SELECT 
        Customer,
        Product,
        Amount,
        ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Product) AS rn
    FROM sales
) t
GROUP BY Customer;

如果你的客户可能有超过2个产品，固定列的写法就不够用了，这时候可以用动态SQL来自动生成对应列名；要是已知最多2个产品，上面的代码完全够用。

SQL Server 实现 #

SQL Server有原生的PIVOT函数，但针对同时转Product和Amount两个字段的场景，用条件聚合反而更直观：

SELECT 
    Customer,
    MAX(CASE WHEN rn = 1 THEN Product END) AS Product,
    MAX(CASE WHEN rn = 1 THEN Amount END) AS Amount,
    MAX(CASE WHEN rn = 2 THEN Product END) AS Product2,
    MAX(CASE WHEN rn = 2 THEN Amount END) AS Amount2
FROM (
    SELECT 
        Customer, Product, Amount,
        ROW_NUMBER() OVER(PARTITION BY Customer ORDER BY Product) rn
    FROM sales
) src
GROUP BY Customer;

当然你也可以用两次PIVOT操作分别处理Product和Amount，但条件聚合的写法更简洁易懂。

PostgreSQL 实现 #

PostgreSQL同样可以用条件聚合来快速实现，思路和上面一致：

SELECT 
    Customer,
    MAX(CASE WHEN rn = 1 THEN Product END) AS Product,
    MAX(CASE WHEN rn = 1 THEN Amount END) AS Amount,
    MAX(CASE WHEN rn = 2 THEN Product END) AS Product2,
    MAX(CASE WHEN rn = 2 THEN Amount END) AS Amount2
FROM (
    SELECT 
        Customer,
        Product,
        Amount,
        ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Product) AS rn
    FROM sales
) t
GROUP BY Customer;

如果你想用PostgreSQL特有的crosstab函数，需要先安装tablefunc扩展，不过条件聚合的写法兼容性更强，不需要额外扩展。

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