SQL查询：如何将同Name不同Item的多行数据转为单行多列展示

这个需求属于典型的行转列场景，可根据你使用的数据库类型选择对应实现方式：

通用兼容方案（支持MySQL8.0+、PostgreSQL、SQL Server、Oracle等所有支持窗口函数的数据库） #

先通过ROW_NUMBER()窗口函数给同一Name下的Item编序号，再通过分组聚合+条件判断把对应序号的值转为独立列：

SELECT
  Name,
  MAX(CASE WHEN rn = 1 THEN Item END) AS `Item 1`,
  MAX(CASE WHEN rn = 2 THEN Item END) AS `Item 2`,
  MAX(CASE WHEN rn = 3 THEN Item END) AS `Item 3`
FROM (
  SELECT 
    Name,
    Item,
    -- 按业务需求排序，如果要匹配示例顺序可自定义排序规则
    ROW_NUMBER() OVER (
      PARTITION BY Name 
      ORDER BY CASE Item WHEN 'Good' THEN 1 WHEN 'Better' THEN 2 WHEN 'Best' THEN 3 END
    ) AS rn
  FROM your_table_name -- 替换为实际表名
) AS temp
GROUP BY Name;

老版本MySQL（不支持窗口函数）实现方案 #

用用户变量实现序号编号：

SET @rn := 0;
SET @prev_name := '';

SELECT
  Name,
  MAX(CASE WHEN rn = 1 THEN Item END) AS `Item 1`,
  MAX(CASE WHEN rn = 2 THEN Item END) AS `Item 2`,
  MAX(CASE WHEN rn = 3 THEN Item END) AS `Item 3`
FROM (
  SELECT 
    Name,
    Item,
    @rn := IF(@prev_name = Name, @rn + 1, 1) AS rn,
    @prev_name := Name
  FROM your_table_name -- 替换为实际表名
  ORDER BY Name, CASE Item WHEN 'Good' THEN 1 WHEN 'Better' THEN 2 WHEN 'Best' THEN 3 END
) AS temp
GROUP BY Name;

动态列扩展方案（Item数量不固定时使用） #

如果每个Name对应的Item数量不固定，需要动态生成列，可以用动态SQL实现，以下是MySQL示例：

SET @sql = NULL;

-- 动态生成所有Item列的查询语句
SELECT GROUP_CONCAT(
  DISTINCT CONCAT('MAX(CASE WHEN rn = ', rn, ' THEN Item END) AS `Item ', rn, '`')
) INTO @sql
FROM (
  SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Item) AS rn 
  FROM your_table_name
) AS temp;

-- 拼接完整查询SQL
SET @sql = CONCAT(
  'SELECT Name, ', @sql, 
  ' FROM (SELECT Name, Item, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Item) AS rn FROM your_table_name) AS temp 
  GROUP BY Name'
);

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

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