如何在SELECT查询中将另一表的值转为新列而非新增行？

原表结构与数据 #

表1（薪资汇总表） #

表2（收入明细表） #

解决方案 #

方法1：CASE WHEN + 聚合函数（通用所有SQL数据库） #

通过CASE WHEN匹配收入类型，配合SUM（或MAX，同一员工同一日期下同一收入类型仅一条数据）聚合，将多行转为多列后与表1关联：

SELECT
    t1.EMPLID,
    t1.CHKDate,
    t1.GrossPay,
    SUM(CASE WHEN t2.EarningCode = 'Regular' THEN t2.Earnings ELSE 0 END) AS Regular_Earnings,
    SUM(CASE WHEN t2.EarningCode = 'PTO' THEN t2.Earnings ELSE 0 END) AS PTO_Earnings,
    SUM(CASE WHEN t2.EarningCode = 'Bonus' THEN t2.Earnings ELSE 0 END) AS Bonus_Earnings
FROM Table1 t1
JOIN Table2 t2 ON t1.EMPLID = t2.EMPLID AND t1.CHKDate = t2.CHKDate
GROUP BY t1.EMPLID, t1.CHKDate, t1.GrossPay;

执行后目标结果：

方法2：PIVOT函数（适用于SQL Server、Oracle等支持该语法的数据库） #

如果你的数据库支持PIVOT，可以用更简洁的语法实现：

SQL Server版本：

SELECT
    p.EMPLID,
    p.CHKDate,
    p.GrossPay,
    p.Regular,
    p.PTO,
    p.Bonus
FROM (
    SELECT
        t1.EMPLID,
        t1.CHKDate,
        t1.GrossPay,
        t2.EarningCode,
        t2.Earnings
    FROM Table1 t1
    JOIN Table2 t2 ON t1.EMPLID = t2.EMPLID AND t1.CHKDate = t2.CHKDate
) AS src
PIVOT (
    SUM(Earnings)
    FOR EarningCode IN ([Regular], [PTO], [Bonus])
) AS p;

Oracle版本：

SELECT
    EMPLID,
    CHKDate,
    GrossPay,
    Regular,
    PTO,
    Bonus
FROM (
    SELECT
        t1.EMPLID,
        t1.CHKDate,
        t1.GrossPay,
        t2.EarningCode,
        t2.Earnings
    FROM Table1 t1
    JOIN Table2 t2 ON t1.EMPLID = t2.EMPLID AND t1.CHKDate = t2.CHKDate
)
PIVOT (
    SUM(Earnings)
    FOR EarningCode IN ('Regular' AS Regular, 'PTO' AS PTO, 'Bonus' AS Bonus)
);

两种方法均可得到单条记录的目标格式，方法1兼容性更强，方法2语法更简洁，可根据使用的数据库选择。

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