如何对account_book_data表执行Pivot查询实现列转行？

下面针对不同主流数据库，给出具体的Pivot实现语句：

MySQL（无原生PIVOT函数，用条件聚合） #

MySQL没有内置PIVOT语法，最通用的方式是使用条件聚合：

SELECT
    Account,
    MAX(CASE WHEN Type = 'Fantasy' THEN Title END) AS Fantasy,
    MAX(CASE WHEN Type = 'Sports' THEN Title END) AS Sports,
    MAX(CASE WHEN Type = 'Biography' THEN Title END) AS Biography
FROM account_book_data
GROUP BY Account;

如果同一个Account下某类Type存在多条记录，需要合并所有Title的话，把MAX换成GROUP_CONCAT即可：

SELECT
    Account,
    GROUP_CONCAT(CASE WHEN Type = 'Fantasy' THEN Title SEPARATOR ', ') AS Fantasy,
    GROUP_CONCAT(CASE WHEN Type = 'Sports' THEN Title SEPARATOR ', ') AS Sports,
    GROUP_CONCAT(CASE WHEN Type = 'Biography' THEN Title SEPARATOR ', ') AS Biography
FROM account_book_data
GROUP BY Account;

SQL Server（原生PIVOT函数） #

SQL Server支持原生PIVOT语法，写法更简洁：

SELECT
    Account,
    Fantasy,
    Sports,
    Biography
FROM account_book_data
PIVOT (
    MAX(Title)
    FOR Type IN ([Fantasy], [Sports], [Biography])
) AS PivotTable;

如果需要合并多记录的Title，可以用STRING_AGG替代MAX（SQL Server 2017及以上版本支持）：

SELECT
    Account,
    Fantasy,
    Sports,
    Biography
FROM (
    SELECT Account, Type, STRING_AGG(Title, ', ') AS Title
    FROM account_book_data
    GROUP BY Account, Type
) AS SourceData
PIVOT (
    MAX(Title)
    FOR Type IN ([Fantasy], [Sports], [Biography])
) AS PivotTable;

PostgreSQL #

PostgreSQL可以用两种方式实现：

方式1：条件聚合（通用） #

和MySQL逻辑一致：

SELECT
    Account,
    MAX(CASE WHEN Type = 'Fantasy' THEN Title END) AS Fantasy,
    MAX(CASE WHEN Type = 'Sports' THEN Title END) AS Sports,
    MAX(CASE WHEN Type = 'Biography' THEN Title END) AS Biography
FROM account_book_data
GROUP BY Account;

合并多记录用STRING_AGG：

SELECT
    Account,
    STRING_AGG(CASE WHEN Type = 'Fantasy' THEN Title END, ', ') AS Fantasy,
    STRING_AGG(CASE WHEN Type = 'Sports' THEN Title END, ', ') AS Sports,
    STRING_AGG(CASE WHEN Type = 'Biography' THEN Title END, ', ') AS Biography
FROM account_book_data
GROUP BY Account;

方式2：使用crosstab函数（专用Pivot工具） #

需要先启用tablefunc扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

然后执行转换：

SELECT *
FROM crosstab(
    'SELECT Account, Type, Title FROM account_book_data ORDER BY 1, 2',
    'SELECT unnest(''{Fantasy,Sports,Biography}''::text[])'
) AS ct(Account text, Fantasy text, Sports text, Biography text);

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