如何用通用SQL查询将键值表转换为键为列名的结构？

嘿，这个场景我太熟了——手动写SQL转置键值表确实能解决一时的问题，但遇到key新增或者变化的情况就很麻烦。下面针对几种主流数据库，给你讲讲通用的动态实现方案，不管表里面有多少种key，都能自动转成宽表，还能把同一个owner+key下的多个value用分号拼接起来：

MySQL里我们可以用GROUP_CONCAT来拼接重复key的value，再结合动态SQL生成列：

-- 第一步：动态生成所有需要转置的列语句
SELECT GROUP_CONCAT(DISTINCT 
    CONCAT('MAX(CASE WHEN `key` = ''', `key`, ''' THEN value END) AS `', `key`, '`')
) INTO @cols FROM your_table;

-- 第二步：拼接完整的查询SQL
SET @sql = CONCAT(
    'SELECT owner, ', @cols, ' 
     FROM (
         SELECT owner, `key`, GROUP_CONCAT(value SEPARATOR '';'') AS value 
         FROM your_table 
         GROUP BY owner, `key`
     ) t 
     GROUP BY owner'
);

-- 第三步：执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

解释：内层先按owner和key分组，把同一个key的所有value拼接成字符串；外层用动态生成的CASE WHEN语句把每个key转成单独的列。

PostgreSQL用string_agg做字符串拼接，结合动态SQL生成列：

-- 第一步：生成列的拼接语句
DO $$
DECLARE cols TEXT;
BEGIN
    SELECT string_agg(DISTINCT 
        format('MAX(CASE WHEN "key" = %L THEN value END) AS %I', "key", "key"), 
        ', '
    ) INTO cols FROM your_table;

    -- 第二步：执行动态查询
    EXECUTE format(
        'SELECT owner, %s 
         FROM (
             SELECT owner, "key", string_agg(value, '';'') AS value 
             FROM your_table 
             GROUP BY owner, "key"
         ) t 
         GROUP BY owner', 
        cols
    );
END $$;

解释：用format函数处理列名的转义，避免特殊字符导致的语法错误，逻辑和MySQL类似，先拼接value再转置列。

SQL Server 2017及以上版本支持STRING_AGG，可以结合PIVOT来简化转置：

-- 第一步：获取所有distinct的key作为列名
DECLARE @cols NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME([key]), ', ') 
FROM (SELECT DISTINCT [key] FROM your_table) t;

-- 第二步：拼接并执行动态SQL
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'
SELECT owner, ' + @cols + N' 
FROM (
    SELECT owner, [key], STRING_AGG(value, '';'') AS value 
    FROM your_table 
    GROUP BY owner, [key]
) t 
PIVOT (
    MAX(value) FOR [key] IN (' + @cols + N')
) p';

EXEC sp_executesql @sql;

解释：内层先拼接同一key的value，然后用PIVOT函数直接把key转成列，比CASE WHEN更简洁。

注意事项 #

• 如果key包含空格、引号等特殊字符，一定要用对应数据库的转义方式（比如MySQL的反引号、SQL Server的QUOTENAME），避免语法错误。
• 字符串拼接函数有长度限制，比如MySQL默认GROUP_CONCAT最大长度是1024，若需要拼接更长内容，可以调整group_concat_max_len参数。
• 不同数据库的版本可能有函数支持差异，比如SQL Server的STRING_AGG需要2017及以上版本。

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