MySQL中如何将分组查询结果转置为单行多列？

我通过以下SQL查询得到了两行两列的结果：

SELECT
a.type,
AVG(DATEDIFF(a.completed, b.created)) AS avg_time_delta
FROM 
a
INNER JOIN a ON b.record_id = content.id
INNER JOIN b ON c.client_id = clients.id
WHERE
record_type = "content"
AND
c.id = "999"
AND a.completed IS NOT NULL
AND b.type = "article"
AND b.spiked = 0
AND (a.type = "TCE sign-off" OR a.type = "Final sign-off") 
GROUP BY a.type

查询结果：

|         type        |  avg_time_delta  |
|---------------------|------------------|
|    Final sign-off   |         20       |
|     TCE sign-off    |         10       |

我需要将结果转置，让列名为「Final sign-off」和「TCE sign-off」，对应单行数据为20和10。尝试用PIVOT实现但报错，我的尝试代码如下，求指导：

SELECT * 
FROM (
    SELECT
    a.type,
AVG(DATEDIFF(a.completed, b.created)) AS avg_time_delta
FROM 
a
INNER JOIN a ON b.record_id = content.id
INNER JOIN b ON c.client_id = clients.id
WHERE
record_type = "content"
AND
c.id = "999"
AND a.completed IS NOT NULL
AND b.type = "article"
AND b.spiked = 0
AND (a.type = "TCE sign-off" OR a.type = "Final sign-off") 
GROUP BY a.type
) AS source_table
PIVOT (
    avg_time_delta
    FOR type in ("Final sign-off", "TCE sign-off")
) AS pivot_table

1. 修正PIVOT写法（以SQL Server为例） #

你的PIVOT代码有两个核心错误：一是未指定聚合函数，二是带空格的列名未用正确标识符包裹。修正后代码如下：

SELECT [Final sign-off], [TCE sign-off]
FROM (
    SELECT
        a.type,
        AVG(DATEDIFF(a.completed, b.created)) AS avg_time_delta
    FROM 
        a
        -- 注意：原始JOIN存在表别名错误，需修正为实际关联的表（比如content而非重复关联a）
        INNER JOIN content ON b.record_id = content.id
        INNER JOIN clients ON c.client_id = clients.id
    WHERE
        record_type = "content"
        AND c.id = "999"
        AND a.completed IS NOT NULL
        AND b.type = "article"
        AND b.spiked = 0
        AND a.type IN ("TCE sign-off", "Final sign-off")
    GROUP BY a.type
) AS source_table
PIVOT (
    -- 由于每个type对应唯一聚合值，用MAX/MIN均可
    MAX(avg_time_delta)
    FOR type IN ([Final sign-off], [TCE sign-off])
) AS pivot_table

2. 通用条件聚合写法（适配所有数据库） #

如果你的数据库不支持原生PIVOT（比如MySQL、PostgreSQL），用条件聚合更通用：

SELECT
    MAX(CASE WHEN a.type = "Final sign-off" THEN avg_time_delta END) AS `Final sign-off`,
    MAX(CASE WHEN a.type = "TCE sign-off" THEN avg_time_delta END) AS `TCE sign-off`
FROM (
    SELECT
        a.type,
        AVG(DATEDIFF(a.completed, b.created)) AS avg_time_delta
    FROM 
        a
        INNER JOIN content ON b.record_id = content.id
        INNER JOIN clients ON c.client_id = clients.id
    WHERE
        record_type = "content"
        AND c.id = "999"
        AND a.completed IS NOT NULL
        AND b.type = "article"
        AND b.spiked = 0
        AND a.type IN ("TCE sign-off", "Final sign-off")
    GROUP BY a.type
) AS source_table

额外提示 #

原始查询中的JOIN语句存在表别名混乱问题（比如INNER JOIN a ON b.record_id = content.id重复关联a表，但条件涉及b和content），需要修正为实际的表关联逻辑，否则查询本身无法正确执行。

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