在BigQuery中如何将指定列转换为值列（列转行）

原数据表：

date_ country category_A category_B
2022-12-11     USA        100        200
2022-12-11  Canada       2000        400

期望结果表：

date_ country   category  value 
2022-12-11     USA category_A    100       
2022-12-11     USA category_B    200        
2022-12-11  Canada category_A   2000   
2022-12-11  Canada category_B    400   

方法1：通用UNION ALL实现（兼容所有SQL数据库） #

这是最基础的跨数据库方案，通过UNION ALL将目标列拆分为多行：

如果直接基于你的查询语句改造：

SELECT date_, country, 'category_A' AS category, category_A AS value
FROM (
    SELECT date('2022-12-11') AS date_, 'USA' AS country, 100 AS category_A, 200 AS category_B
    UNION ALL
    SELECT date('2022-12-11') AS date_, 'Canada' AS country, 2000 AS category_A, 400 AS category_B
) AS original_table
UNION ALL
SELECT date_, country, 'category_B' AS category, category_B AS value
FROM (
    SELECT date('2022-12-11') AS date_, 'USA' AS country, 100 AS category_A, 200 AS category_B
    UNION ALL
    SELECT date('2022-12-11') AS date_, 'Canada' AS country, 2000 AS category_A, 400 AS category_B
) AS original_table
ORDER BY date_, country, category;

如果原数据已经是一张物理表（比如名为your_table），可以简化为：

SELECT date_, country, 'category_A' AS category, category_A AS value
FROM your_table
UNION ALL
SELECT date_, country, 'category_B' AS category, category_B AS value
FROM your_table
ORDER BY date_, country, category;

方法2：使用UNPIVOT语法（现代数据库简化版） #

多数现代数据库（如BigQuery、SQL Server、Oracle等）支持UNPIVOT关键字，写法更简洁：

BigQuery 版本 #

WITH original_table AS (
    SELECT date('2022-12-11') AS date_, 'USA' AS country, 100 AS category_A, 200 AS category_B
    UNION ALL
    SELECT date('2022-12-11') AS date_, 'Canada' AS country, 2000 AS category_A, 400 AS category_B
)
SELECT date_, country, category, value
FROM original_table
UNPIVOT (
    value FOR category IN (category_A, category_B)
)
ORDER BY date_, country, category;

SQL Server 版本 #

WITH original_table AS (
    SELECT CAST('2022-12-11' AS DATE) AS date_, 'USA' AS country, 100 AS category_A, 200 AS category_B
    UNION ALL
    SELECT CAST('2022-12-11' AS DATE) AS date_, 'Canada' AS country, 2000 AS category_A, 400 AS category_B
)
SELECT date_, country, category, value
FROM original_table
UNPIVOT (
    value FOR category IN ([category_A], [category_B])
) AS unpvt
ORDER BY date_, country, category;

PostgreSQL 版本（通过JSON函数实现） #

PostgreSQL无原生UNPIVOT，可借助JSON函数模拟：

WITH original_table AS (
    SELECT '2022-12-11'::DATE AS date_, 'USA' AS country, 100 AS category_A, 200 AS category_B
    UNION ALL
    SELECT '2022-12-11'::DATE AS date_, 'Canada' AS country, 2000 AS category_A, 400 AS category_B
)
SELECT 
    date_,
    country,
    (jsonb_each_text(jsonb_build_object('category_A', category_A, 'category_B', category_B))).key AS category,
    (jsonb_each_text(jsonb_build_object('category_A', category_A, 'category_B', category_B))).value::INT AS value
FROM original_table
ORDER BY date_, country, category;

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