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
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.