时间戳

首页 > 解决方案 > 在 Oracle 中使用动态列进行透视

sql - 在 Oracle 中使用动态列进行透视

问题描述

我是新的 oracle 数据库我有一个如下表

ID     Passengers Age Eligible
123456 Ben        65  Yes
123456 Mary       58  Yes
123458 Stephanie  37  Yes
123458 Aaron      32  Yes
123458 Caroline   18  No

我想获得动态列名称为 Age1、Age2、Age3 等的结果。如下所示

ID      Age1 Age2 Age3
123456  65   58   NULL
123458  37   32   18

它可以通过 STUFF 和 Dynamic Pivot 来实现，SQL Server但我不知道如何在Oracle

谁能指导我如何动态地做到这一点Oracle

标签： sqloracleplsqlpivotdynamic-pivot

解决方案

SYS_REFCURSOR在存储函数中（例如，使用 PL/SQL 而不是直接使用 SQL）可以用于获取动态生成的结果集（例如Dynamic Pivot）。在这种情况下，会生成一个用于条件聚合的字符串：

CREATE OR REPLACE FUNCTION get_passengers_rs RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_str       VARCHAR2(32767);
BEGIN
  SELECT LISTAGG('MAX(CASE WHEN rn = '||lvl||' THEN age||''(''||passengers||'')'' END) 
                   AS "Age'||lvl||'"' ,',') WITHIN GROUP (ORDER BY 0)
    INTO v_str
    FROM ( SELECT level AS lvl  
             FROM dual
          CONNECT BY level <= (SELECT MAX(COUNT(*)) FROM t GROUP BY ID ) ) t;  

  v_sql :=
  'SELECT ID, '|| v_str ||'
     FROM 
     (
      SELECT t.*, 
             ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 0) AS rn
        FROM t     
      )
    GROUP BY ID';

  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;

我还添加了乘客的姓名，以便很好地区分每个数据。

然后运行以下代码：

VAR rc REFCURSOR
EXEC :rc := get_passengers_rs;
PRINT rc

从 SQL Developer 的命令行中查看预期的结果集。

上面的代码为当前存在的数据生成这个 SQL 字符串（v_sql ）

SELECT ID, MAX(CASE WHEN rn = 1 THEN age||'('||passengers||')' END) AS "Age1",
           MAX(CASE WHEN rn = 2 THEN age||'('||passengers||')' END) AS "Age2",
           MAX(CASE WHEN rn = 3 THEN age||'('||passengers||')' END) AS "Age3"
  FROM 
     (
      SELECT t.*, 
             ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 0) AS rn
        FROM t     
      )
 GROUP BY ID

产生

ID      Age1        Age2            Age3
123456  58(Marie)   65(Ben) 
123458  32(Aaron)   18(Caroline)    37(Stephanie)

作为结果集。

推荐阅读