面试题：如何使用JOIN实现SQL数据透视（PIVOT）？

面试中要求用基础SQL对员工数据做透视，一开始我知道可以用CASE表达式结合MAX/SUM函数实现，但面试官表示可以通过JOIN完成，下面是用JOIN实现该透视的方法。

原始数据表 #

建表及插入数据代码 #

CREATE TABLE Employees(emp_id INT, col_id INT, col_desc NVARCHAR(MAX), attvalue INT, month DATE);

INSERT INTO Employees
VALUES
(1,1,'salary',2000,'2010-05-09'),
(1,2,'bonus',0,'2010-05-09'),
(1,3,'compensation',2000,'2010-05-09'),
(1,1,'salary',2000,'2010-05-10'),
(1,2,'bonus',500,'2010-05-10'),
(1,3,'compensation',2500,'2010-05-10'),
(2,1,'salary',1000,'2010-05-09'),
(2,2,'bonus',500,'2010-05-09'),
(2,3,'compensation',1500,'2010-05-09');

目标透视表 #

使用JOIN实现透视的SQL代码 #

核心思路是基于emp_id和month这两个分组字段，分别将每个属性（salary、bonus、compensation）对应的数据集通过JOIN关联起来，从而将行转列：

SELECT 
    s.emp_id,
    s.month,
    s.attvalue AS salary,
    b.attvalue AS bonus,
    c.attvalue AS compensation
FROM 
    Employees s
JOIN 
    Employees b ON s.emp_id = b.emp_id AND s.month = b.month AND b.col_desc = 'bonus'
JOIN 
    Employees c ON s.emp_id = c.emp_id AND s.month = c.month AND c.col_desc = 'compensation'
WHERE 
    s.col_desc = 'salary';

代码说明 #

• 首先以salary对应的数据集作为主表，通过emp_id和month关联bonus数据集，获取每个员工对应日期的奖金；
• 再用同样的关联条件关联compensation数据集，获取薪酬总额；
• 最后筛选主表中col_desc为salary的记录，确保每一行对应一个员工的一个日期维度，同时带出其他属性值。

如果存在某个员工某日期下缺失某个属性的情况，可以将JOIN替换为LEFT JOIN，避免数据丢失。

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