未知item数量下，如何用SQL动态生成列实现行转列

由于SQL本身是静态查询语言，要实现未知数量item自动生成对应列的需求，必须借助各数据库的动态SQL能力。以下是主流数据库的最佳实现方案，均满足你提出的所有要求：

核心思路 #

通过动态拼接SQL语句，将每个item对应的height和width转换为单独列，最终按index分组聚合，保证index唯一连续。

MySQL/MariaDB 实现 #

使用GROUP_CONCAT拼接透视列，结合预处理语句执行动态SQL：

-- 1. 动态生成所有item对应的列片段
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT(
    'MAX(CASE WHEN item = ''', item, ''' THEN height END) AS `', item, '.height`,',
    'MAX(CASE WHEN item = ''', item, ''' THEN width END) AS `', item, '.width`'
  )
) INTO @sql
FROM your_table; -- 替换为你的表名

-- 2. 拼接完整查询语句（可添加WHERE条件）
SET @sql = CONCAT(
  'SELECT `index`, ', @sql, ' 
   FROM your_table 
   WHERE `index` BETWEEN 1 AND 6 -- 可选：按需求添加切片条件
   GROUP BY `index` 
   ORDER BY `index`'
);

-- 3. 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server 实现 #

使用STRING_AGG（2017+版本）拼接列片段，通过sp_executesql执行动态SQL：

DECLARE @sql NVARCHAR(MAX);

-- 1. 动态生成所有item对应的列片段
SELECT @sql = STRING_AGG(
  CONCAT(
    'MAX(CASE WHEN item = ''', item, ''' THEN height END) AS [', item, '.height],',
    'MAX(CASE WHEN item = ''', item, ''' THEN width END) AS [', item, '.width]'
  ), ','
)
FROM (SELECT DISTINCT item FROM your_table) AS items; -- 替换为你的表名

-- 2. 拼接完整查询语句（可添加WHERE条件）
SET @sql = N'SELECT [index], ' + @sql + N' 
             FROM your_table 
             WHERE [index] BETWEEN 1 AND 6 -- 可选：按需求添加切片条件
             GROUP BY [index] 
             ORDER BY [index]';

-- 3. 执行动态SQL
EXEC sp_executesql @sql;

PostgreSQL 实现 #

使用string_agg和format函数安全拼接列片段，通过匿名块执行动态SQL：

DO $$
DECLARE
  sql TEXT;
BEGIN
  -- 1. 动态生成所有item对应的列片段
  SELECT string_agg(
    format(
      'MAX(CASE WHEN item = %L THEN height END) AS "%1$s.height", MAX(CASE WHEN item = %L THEN width END) AS "%1$s.width"',
      item
    ), ','
  ) INTO sql
  FROM (SELECT DISTINCT item FROM your_table) AS items; -- 替换为你的表名

  -- 2. 拼接完整查询语句（可添加WHERE条件）
  sql := 'SELECT "index", ' || sql || ' 
          FROM your_table 
          WHERE "index" BETWEEN 1 AND 6 -- 可选：按需求添加切片条件
          GROUP BY "index" 
          ORDER BY "index"';

  -- 3. 执行动态SQL
  EXECUTE sql;
END $$;

方案满足性验证 #

1. index唯一连续：通过GROUP BY index和ORDER BY index保证每个index仅出现一次，且按顺序排列。
2. 支持切片查询：在动态SQL的FROM子句后添加WHERE index BETWEEN ...即可实现。
3. 单次返回所有数据：动态SQL执行后一次性输出所有透视后的结果。
4. 不修改原表：所有操作基于原表查询，无任何结构或数据变更。

注意事项 #

• 若item名称包含特殊字符（空格、引号等），示例中已通过反引号/方括号/双引号处理列名合法性。
• 若item值来自用户输入，需额外处理SQL注入风险（如使用参数化查询或字符转义）。

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