Oracle 11g中实现动态列透视查询的方法咨询

是否可行？ #

可行，但无法通过静态SQL实现自动新增列，必须使用动态SQL生成适配当前数据的查询语句——静态SQL的列数在编写时就固定了，无法根据后续数据变化自动扩展。

实现思路 #

核心逻辑是：先统计每种药水对应的试剂数量，找到最大的试剂个数N，动态生成REAG_1到REAG_N的列名，再通过行转列的方式将试剂信息映射到对应列中。

具体实现（以SQL Server为例） #

1. 给每个药水的试剂按顺序编号
   用ROW_NUMBER()函数按药水ID分区、试剂ID排序，为每个试剂分配序号：

   SELECT 
       p.DESCRIPTION AS POTION,
       r.DESCRIPTION AS REAGENT,
       ROW_NUMBER() OVER (PARTITION BY p.ID ORDER BY r.ID) AS RN
   FROM POTIONS p
   JOIN POTION_REAGENTS pr ON p.ID = pr.ID_POTION
   JOIN REAGENTS r ON pr.ID_REAGENT = r.ID
   

   执行后会得到每个药水的试剂及其对应的序号（如RN=1、RN=2）。

2. 动态生成列名与完整查询语句
   先获取最大的试剂序号，再拼接所有REAG_*列，最后执行动态SQL：

   DECLARE @max_rn INT, @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)
   
   -- 获取当前数据中最大的试剂序号
   SELECT @max_rn = MAX(RN)
   FROM (
       SELECT ROW_NUMBER() OVER (PARTITION BY p.ID ORDER BY r.ID) AS RN
       FROM POTIONS p
       JOIN POTION_REAGENTS pr ON p.ID = pr.ID_POTION
       JOIN REAGENTS r ON pr.ID_REAGENT = r.ID
   ) t
   
   -- 生成REAG_1、REAG_2...REAG_N的列名字符串
   SET @cols = STUFF((SELECT ',' + QUOTENAME('REAG_' + CAST(RN AS VARCHAR))
                     FROM (SELECT DISTINCT RN FROM (
                         SELECT ROW_NUMBER() OVER (PARTITION BY p.ID ORDER BY r.ID) AS RN
                         FROM POTIONS p
                         JOIN POTION_REAGENTS pr ON p.ID = pr.ID_POTION
                         JOIN REAGENTS r ON pr.ID_REAGENT = r.ID
                     ) t) rn_list
                     ORDER BY RN
                     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
   
   -- 拼接并执行动态透视SQL
   SET @sql = N'
   SELECT POTION, ' + @cols + N'
   FROM (
       SELECT 
           p.DESCRIPTION AS POTION,
           r.DESCRIPTION AS REAGENT,
           ''REAG_'' + CAST(ROW_NUMBER() OVER (PARTITION BY p.ID ORDER BY r.ID) AS VARCHAR) AS REAG_COL
       FROM POTIONS p
       JOIN POTION_REAGENTS pr ON p.ID = pr.ID_POTION
       JOIN REAGENTS r ON pr.ID_REAGENT = r.ID
   ) src
   PIVOT (
       MAX(REAGENT)
       FOR REAG_COL IN (' + @cols + N')
   ) piv
   ORDER BY POTION'
   
   EXEC sp_executesql @sql
   

   当新增药水包含4种试剂时，@max_rn会自动变为4，动态生成REAG_4列，查询结果会自动包含该列。

其他数据库的实现差异 #

• MySQL：用GROUP_CONCAT生成列名，结合PREPARE和EXECUTE执行动态SQL，逻辑一致但语法略有不同。
• PostgreSQL：可使用crosstab函数配合动态SQL，或通过条件聚合的方式动态生成列。

注意事项 #

• 试剂的排序逻辑可按需调整（比如按试剂名称排序），只需修改ROW_NUMBER()中的ORDER BY子句即可。
• 动态SQL需确保执行权限充足，本场景中列名从系统数据生成，SQL注入风险较低。

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