SQL中对不存在的键值执行PIVOT转换的实现方法问询

原表结构与数据 #

test_table
-------------------
key      value       
'a'      'apple'     
'b'      'ball'
'c'       null
'd'       ''

目标转换结果 #

pivot_table
------------------------------------
    a        b       c      d      e         f
'apple'  'ball'  null          'N/A'     'N/A'

核心需求：将指定的6个键（a、b、c、d、e、f）转成列，原表不存在的键（e、f）对应值显示为'N/A'，原表已有的键保留原始值（包括null和空字符串）。

各数据库实现方案 #

1. SQL Server #

通过CTE生成所有目标键，再结合条件聚合实现转换：

WITH all_keys AS (
    SELECT 'a' AS key UNION ALL
    SELECT 'b' UNION ALL
    SELECT 'c' UNION ALL
    SELECT 'd' UNION ALL
    SELECT 'e' UNION ALL
    SELECT 'f'
)
SELECT
    MAX(CASE WHEN ak.key = 'a' THEN t.value END) AS a,
    MAX(CASE WHEN ak.key = 'b' THEN t.value END) AS b,
    MAX(CASE WHEN ak.key = 'c' THEN t.value END) AS c,
    MAX(CASE WHEN ak.key = 'd' THEN t.value END) AS d,
    MAX(CASE WHEN ak.key = 'e' THEN COALESCE(t.value, 'N/A') END) AS e,
    MAX(CASE WHEN ak.key = 'f' THEN COALESCE(t.value, 'N/A') END) AS f
FROM all_keys ak
LEFT JOIN test_table t ON ak.key = t.key;

2. PostgreSQL #

方式一：条件聚合（无需扩展）

WITH all_keys AS (
    SELECT unnest(ARRAY['a','b','c','d','e','f']) AS key
)
SELECT
    MAX(CASE WHEN ak.key = 'a' THEN t.value END) AS a,
    MAX(CASE WHEN ak.key = 'b' THEN t.value END) AS b,
    MAX(CASE WHEN ak.key = 'c' THEN t.value END) AS c,
    MAX(CASE WHEN ak.key = 'd' THEN t.value END) AS d,
    MAX(CASE WHEN ak.key = 'e' THEN COALESCE(t.value, 'N/A') END) AS e,
    MAX(CASE WHEN ak.key = 'f' THEN COALESCE(t.value, 'N/A') END) AS f
FROM all_keys ak
LEFT JOIN test_table t ON ak.key = t.key;

方式二：使用crosstab函数（需安装tablefunc扩展）

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

WITH all_keys AS (
    SELECT unnest(ARRAY['a','b','c','d','e','f']) AS key
),
source_data AS (
    SELECT 
        ak.key, 
        CASE 
            WHEN ak.key IN ('e','f') THEN COALESCE(t.value, 'N/A') 
            ELSE t.value 
        END AS value
    FROM all_keys ak
    LEFT JOIN test_table t ON ak.key = t.key
)
SELECT * FROM crosstab(
    'SELECT 1 AS row_id, key, value FROM source_data ORDER BY key',
    'SELECT unnest(ARRAY[''a'',''b'',''c'',''d'',''e'',''f''])'
) AS ct(row_id INT, a VARCHAR(10), b VARCHAR(10), c VARCHAR(10), d VARCHAR(10), e VARCHAR(10), f VARCHAR(10));

3. MySQL #

利用CTE生成目标键，结合条件聚合实现：

WITH all_keys AS (
    SELECT 'a' AS key UNION ALL
    SELECT 'b' UNION ALL
    SELECT 'c' UNION ALL
    SELECT 'd' UNION ALL
    SELECT 'e' UNION ALL
    SELECT 'f'
)
SELECT
    MAX(CASE WHEN ak.key = 'a' THEN t.value END) AS a,
    MAX(CASE WHEN ak.key = 'b' THEN t.value END) AS b,
    MAX(CASE WHEN ak.key = 'c' THEN t.value END) AS c,
    MAX(CASE WHEN ak.key = 'd' THEN t.value END) AS d,
    MAX(CASE WHEN ak.key = 'e' THEN COALESCE(t.value, 'N/A') END) AS e,
    MAX(CASE WHEN ak.key = 'f' THEN COALESCE(t.value, 'N/A') END) AS f
FROM all_keys ak
LEFT JOIN test_table t ON ak.key = t.key;

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