如何从表数据生成动态列？无需手动枚举列的SQL方案

我完全懂这种痛点——每次新增一个domain值就得手动修改SQL添加列，不仅繁琐还容易出错。其实所有主流数据库都支持动态生成行转列SQL的方案，不用硬编码枚举列，下面分几种常用数据库给你具体实现：

MySQL 实现 #

核心思路是用GROUP_CONCAT拼接动态的CASE WHEN语句，再通过预处理语句执行：

-- 第一步：动态生成所有domain对应的列处理逻辑
SELECT GROUP_CONCAT(DISTINCT CONCAT(
  'MAX(CASE WHEN domain = ''', domain, ''' THEN value END) AS `', domain, '`'
)) INTO @cols
FROM TABLE;

-- 第二步：拼接完整的查询SQL
SET @sql = CONCAT(
  'SELECT `key`, ', @cols, ' FROM TABLE GROUP BY `key`'
);

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

当你的表新增domain值（比如fr）时，这个SQL会自动把fr作为新列包含进去，完全不用手动修改代码。

PostgreSQL 实现 #

PostgreSQL有两种常用方式：一种是用动态SQL拼接，另一种是借助tablefunc扩展的crosstab函数。

方式1：动态SQL拼接 #

-- 第一步：获取所有distinct的domain并拼接列逻辑
WITH domains AS (
  SELECT DISTINCT domain FROM TABLE
)
SELECT string_agg(
  'MAX(CASE WHEN domain = ''' || domain || ''' THEN value END) AS "' || domain || '"',
  ', '
) INTO cols
FROM domains;

-- 第二步：执行动态生成的SQL
EXECUTE format('SELECT "key", %s FROM TABLE GROUP BY "key"', cols);

方式2：使用crosstab函数（需先安装扩展） #

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

-- 动态生成crosstab的列定义
WITH domains AS (
  SELECT DISTINCT domain FROM TABLE ORDER BY domain
)
SELECT string_agg(domain || ' text', ', ') INTO col_def
FROM domains;

-- 执行crosstab查询
EXECUTE format('
  SELECT * FROM crosstab(
    ''SELECT "key", domain, value FROM TABLE ORDER BY 1,2'',
    ''SELECT DISTINCT domain FROM TABLE ORDER BY 1''
  ) AS ct("key" text, %s)', col_def);

SQL Server 实现 #

用STUFF和FOR XML PATH来拼接动态列逻辑，再通过sp_executesql执行：

DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 第一步：动态生成列处理逻辑
SELECT @cols = STUFF(
  (SELECT DISTINCT ', MAX(CASE WHEN domain = ''' + domain + ''' THEN value END) AS [' + domain + ']'
   FROM TABLE
   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
  1, 1, ''
);

-- 第二步：拼接并执行完整SQL
SET @sql = 'SELECT [key], ' + @cols + ' FROM TABLE GROUP BY [key]';
EXEC sp_executesql @sql;

核心思路总结 #

不管用哪种数据库，本质都是两步：

• 先从表中获取所有唯一的domain值
• 把这些值动态拼接成行转列的逻辑（比如CASE WHEN），组成完整的SQL后执行

这样就能实现完全动态的列生成，再也不用手动枚举en、de这类列名了！

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