Pivot with Dynamic Columns in Oracle

Question

I am new oracle database I have a table like below

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

I want to get the result with dynamic column name as Age1,Age2,Age3 etc.. as below

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

It can be achieved with STUFF and Dynamic Pivot in SQL Server but I am not aware how to achieve this in Oracle

Can anyone please guide me how to do it dynamically in Oracle

Answer 1

SYS_REFCURSOR within a stored function(eg. PL/SQL used instead of using SQL directly) might be used in order to get dynamically generated result set(eg. Dynamic Pivot). In this case, a string for Conditional Aggregation is generated :

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;

I also added the names of passengers in order to distinguish each data well.

Then run the below code :

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

from SQL Developer's Command Line in order to see the expected result set.

Above code generates this SQL string(v_sql) for the currently existing data

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

which yields

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

as result set.