如何在同一张表中根据另一列条件实现行转列查询

原始表结构 #

emp表数据如下：

期望结果 #

需要将行转列为如下格式：

错误的查询语句 #

用户尝试的SQL：

SELECT DISTINCT(e1.empid) AS empid,
(SELECT e2.empstartedfrom FROM emp WHERE empprojectname='P1' LIMIT 1) AS P1projectstartedfrom, 
(SELECT e2.empstartedfrom FROM emp WHERE empprojectname='P2' LIMIT 1) AS P2projectstartedfrom,
(SELECT e2.empstartedfrom FROM emp WHERE empprojectname='P3' LIMIT 1) AS P3projectstartedfrom,
(SELECT e2.empstartedfrom FROM emp WHERE empprojectname='P4' LIMIT 1) AS P4projectstartedfrom,
(SELECT e2.empstartedfrom FROM emp WHERE empprojectname='P5' LIMIT 1) AS P5projectstartedfrom
FROM emp e1 INNER JOIN emp e2 ON e1.empid=e2.empid;

错误原因 #

子查询未关联外层的empid，导致每个项目取到的是全表中该项目的第一条数据，而非当前员工对应的项目日期，最终所有员工的同项目日期完全一致。

修正后的查询语句 #

方法1：关联子查询（添加empid过滤） #

SELECT DISTINCT e1.empid AS empid,
(SELECT e2.empstartedfrom FROM emp e2 WHERE e2.empprojectname='P1' AND e2.empid = e1.empid) AS P1projectstartedfrom,
(SELECT e2.empstartedfrom FROM emp e2 WHERE e2.empprojectname='P2' AND e2.empid = e1.empid) AS P2projectstartedfrom,
(SELECT e2.empstartedfrom FROM emp e2 WHERE e2.empprojectname='P3' AND e2.empid = e1.empid) AS P3projectstartedfrom,
(SELECT e2.empstartedfrom FROM emp e2 WHERE e2.empprojectname='P4' AND e2.empid = e1.empid) AS P4projectstartedfrom,
(SELECT e2.empstartedfrom FROM emp e2 WHERE e2.empprojectname='P5' AND e2.empid = e1.empid) AS P5projectstartedfrom
FROM emp e1;

方法2：条件聚合（性能更优） #

SELECT 
    empid,
    MAX(CASE WHEN empprojectname = 'P1' THEN empstartedfrom END) AS P1projectstartedfrom,
    MAX(CASE WHEN empprojectname = 'P2' THEN empstartedfrom END) AS P2projectstartedfrom,
    MAX(CASE WHEN empprojectname = 'P3' THEN empstartedfrom END) AS P3projectstartedfrom,
    MAX(CASE WHEN empprojectname = 'P4' THEN empstartedfrom END) AS P4projectstartedfrom,
    MAX(CASE WHEN empprojectname = 'P5' THEN empstartedfrom END) AS P5projectstartedfrom
FROM emp
GROUP BY empid;

两种方法均可得到预期的行转列结果，其中条件聚合在数据量较大时性能表现更优。

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