PostgreSQL中如何实现pivot行转列操作？

1 现有静态写法修复 #

你当前写法出现大量空值的核心原因是缺少聚合操作与分组逻辑，正确的固定Type场景的Pivot写法如下：

SELECT
    year,
    month,
    week,
    MAX(CASE WHEN type = 'A' THEN count END) AS A,
    MAX(CASE WHEN type = 'B' THEN count END) AS B,
    MAX(CASE WHEN type = 'C' THEN count END) AS C
FROM 
    your_table_name
GROUP BY 
    year, month, week
ORDER BY 
    year, month, week

该写法通过MAX聚合同分组下的多行列数据，自动合并非空值，不会再出现空列问题。

2 动态Pivot实现（无需手动新增Type列） #

如果需要适配后续新增的Type类型，不用手动修改SQL列定义，可使用各数据库支持的动态SQL能力实现，主流数据库的实现方式如下：

MySQL 实现 #

-- 拼接生成动态列的查询逻辑
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN type = ''', type, ''' THEN count END) AS ', type)
) INTO @sql
FROM your_table_name;

-- 拼接完整查询语句
SET @sql = CONCAT('SELECT year, month, week, ', @sql, ' FROM your_table_name GROUP BY year, month, week ORDER BY year, month, week');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Hive/Spark SQL 实现 #

-- 直接调用内置PIVOT函数，自动适配所有存在的Type值
SELECT *
FROM your_table_name
PIVOT (
    MAX(count) 
    FOR type IN (SELECT DISTINCT type FROM your_table_name)
) AS pivot_table
ORDER BY year, month, week;

PostgreSQL 实现 #

-- 首先安装tablefunc扩展（仅首次执行需要）
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 调用crosstab函数实现动态行转列
SELECT *
FROM crosstab(
    'SELECT year, month, week, type, count FROM your_table_name ORDER BY 1,2,3',
    'SELECT DISTINCT type FROM your_table_name ORDER BY 1'
) AS ct(year INT, month INT, week INT, A INT, B INT, C INT);

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