如何在PostgreSQL中将表列转换为行值（列转行）

原表信息 #

我有一张名为fund_flag的表，结构及数据如下：

需求输出格式 #

需要将上述宽表转换为以下长表格式：

实现SQL语句 #

通用兼容版（适用于多数数据库：MySQL、SQL Server、Oracle等） #

使用UNION ALL将每个fund列拆分为单独行：

SELECT fund_type, 'fund1' AS fund_name, fund1 AS fund_value, sign_val
FROM fund_flag
UNION ALL
SELECT fund_type, 'fund2' AS fund_name, fund2 AS fund_value, sign_val
FROM fund_flag
UNION ALL
SELECT fund_type, 'fund3' AS fund_name, fund3 AS fund_value, sign_val
FROM fund_flag
UNION ALL
SELECT fund_type, 'fund4' AS fund_name, fund4 AS fund_value, sign_val
FROM fund_flag
UNION ALL
SELECT fund_type, 'fund5' AS fund_name, fund5 AS fund_value, sign_val
FROM fund_flag
UNION ALL
SELECT fund_type, 'fund6' AS fund_name, fund6 AS fund_value, sign_val
FROM fund_flag
ORDER BY fund_type, fund_name;

数据库专属简化版（可选） #

MySQL 8.0+/PostgreSQL

利用数组拆分简化语句：

-- MySQL 8.0+
SELECT 
    ff.fund_type,
    f.fund_name,
    CASE f.fund_name
        WHEN 'fund1' THEN ff.fund1
        WHEN 'fund2' THEN ff.fund2
        WHEN 'fund3' THEN ff.fund3
        WHEN 'fund4' THEN ff.fund4
        WHEN 'fund5' THEN ff.fund5
        WHEN 'fund6' THEN ff.fund6
    END AS fund_value,
    ff.sign_val
FROM fund_flag ff
CROSS JOIN UNNEST(['fund1','fund2','fund3','fund4','fund5','fund6']) AS f(fund_name)
ORDER BY ff.fund_type, f.fund_name;

-- PostgreSQL
SELECT 
    ff.fund_type,
    f.fund_name,
    CASE f.fund_name
        WHEN 'fund1' THEN ff.fund1
        WHEN 'fund2' THEN ff.fund2
        WHEN 'fund3' THEN ff.fund3
        WHEN 'fund4' THEN ff.fund4
        WHEN 'fund5' THEN ff.fund5
        WHEN 'fund6' THEN ff.fund6
    END AS fund_value,
    ff.sign_val
FROM fund_flag ff
CROSS JOIN UNNEST(ARRAY['fund1','fund2','fund3','fund4','fund5','fund6']) AS f(fund_name)
ORDER BY ff.fund_type, f.fund_name;

SQL Server/Oracle

使用UNPIVOT专用操作符：

-- SQL Server
SELECT fund_type, fund_name, fund_value, sign_val
FROM fund_flag
UNPIVOT (
    fund_value FOR fund_name IN (fund1, fund2, fund3, fund4, fund5, fund6)
) AS up
ORDER BY fund_type, fund_name;

-- Oracle
SELECT fund_type, fund_name, fund_value, sign_val
FROM fund_flag
UNPIVOT (
    fund_value FOR fund_name IN (fund1, fund2, fund3, fund4, fund5, fund6)
)
ORDER BY fund_type, fund_name;

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