Oracle PL/SQL实现单列值转多列的查询需求求助

核心思路是先给每个Person ID下的爱好按升序分配唯一行号，再通过条件聚合或PIVOT将行号映射为对应的列。以下是不同数据库的具体实现：

通用条件聚合方案（适用于绝大多数数据库） #

这种方式兼容性强，无需依赖数据库特定的PIVOT语法：

SELECT 
    Person_ID,
    MAX(CASE WHEN hobby_rank = 1 THEN Hobby END) AS Hobby_1,
    MAX(CASE WHEN hobby_rank = 2 THEN Hobby END) AS Hobby_2,
    MAX(CASE WHEN hobby_rank = 3 THEN Hobby END) AS Hobby_3,
    MAX(CASE WHEN hobby_rank = 4 THEN Hobby END) AS Hobby_4,
    MAX(CASE WHEN hobby_rank = 5 THEN Hobby END) AS Hobby_5,
    MAX(CASE WHEN hobby_rank = 6 THEN Hobby END) AS Hobby_6
FROM (
    -- 内层查询：给每个用户的爱好按升序分配行号
    SELECT 
        Person_ID,
        Hobby,
        ROW_NUMBER() OVER (PARTITION BY Person_ID ORDER BY Hobby ASC) AS hobby_rank
    FROM your_table_name
) ranked_hobbies
GROUP BY Person_ID
ORDER BY Person_ID;

这里用MAX()聚合是因为每个hobby_rank对应用户的唯一爱好，MAX/MIN不会改变结果，仅满足聚合函数的语法要求。

数据库专属PIVOT方案 #

Oracle #

SELECT *
FROM (
    SELECT 
        Person_ID,
        Hobby,
        'Hobby ' || ROW_NUMBER() OVER (PARTITION BY Person_ID ORDER BY Hobby ASC) AS hobby_column
    FROM your_table_name
)
PIVOT (
    MAX(Hobby) FOR hobby_column IN (
        'Hobby 1' AS Hobby_1,
        'Hobby 2' AS Hobby_2,
        'Hobby 3' AS Hobby_3,
        'Hobby 4' AS Hobby_4,
        'Hobby 5' AS Hobby_5,
        'Hobby 6' AS Hobby_6
    )
)
ORDER BY Person_ID;

MySQL 8.0+ #

SELECT 
    Person_ID,
    Hobby_1,
    Hobby_2,
    Hobby_3,
    Hobby_4,
    Hobby_5,
    Hobby_6
FROM (
    SELECT 
        Person_ID,
        Hobby,
        CONCAT('Hobby_', ROW_NUMBER() OVER (PARTITION BY Person_ID ORDER BY Hobby ASC)) AS hobby_column
    FROM your_table_name
) ranked_hobbies
PIVOT (
    MAX(Hobby)
    FOR hobby_column IN ('Hobby_1', 'Hobby_2', 'Hobby_3', 'Hobby_4', 'Hobby_5', 'Hobby_6')
) AS pivot_table
ORDER BY Person_ID;

每日自动运行设置 #

根据数据库类型配置定时任务：

• Oracle：使用DBMS_SCHEDULER创建定时作业，指定每日执行时间，将查询结果写入目标表。
• SQL Server：通过SQL Server Agent创建作业，设置每日调度，执行上述转换查询。
• MySQL：开启事件调度器后创建定时事件：

  SET GLOBAL event_scheduler = ON;
  
  CREATE EVENT daily_hobby_transform
  ON SCHEDULE EVERY 1 DAY
  STARTS DATE_ADD(CURDATE(), INTERVAL 1 DAY) -- 从次日开始每日执行
  DO
  BEGIN
      -- 将结果写入目标表，存在则更新
      INSERT INTO target_hobby_table (Person_ID, Hobby_1, Hobby_2, Hobby_3, Hobby_4, Hobby_5, Hobby_6)
      SELECT 
          Person_ID,
          MAX(CASE WHEN hobby_rank = 1 THEN Hobby END) AS Hobby_1,
          MAX(CASE WHEN hobby_rank = 2 THEN Hobby END) AS Hobby_2,
          MAX(CASE WHEN hobby_rank = 3 THEN Hobby END) AS Hobby_3,
          MAX(CASE WHEN hobby_rank = 4 THEN Hobby END) AS Hobby_4,
          MAX(CASE WHEN hobby_rank = 5 THEN Hobby END) AS Hobby_5,
          MAX(CASE WHEN hobby_rank = 6 THEN Hobby END) AS Hobby_6
      FROM (
          SELECT Person_ID, Hobby, ROW_NUMBER() OVER (PARTITION BY Person_ID ORDER BY Hobby ASC) AS hobby_rank
          FROM your_table_name
      ) t
      GROUP BY Person_ID
      ON DUPLICATE KEY UPDATE 
          Hobby_1 = VALUES(Hobby_1),
          Hobby_2 = VALUES(Hobby_2),
          Hobby_3 = VALUES(Hobby_3),
          Hobby_4 = VALUES(Hobby_4),
          Hobby_5 = VALUES(Hobby_5),
          Hobby_6 = VALUES(Hobby_6);
  END;
  

为什么之前的方案失效？ #

• 仅排序取第一行只能获取单个爱好，无法覆盖多行数据；
• 常规PIVOT用求和是错误的，因为我们需要的是字符串类型的爱好值，用MAX()/MIN()聚合字符串即可实现行转列的需求，本质就是LISTAGG的逆操作。

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