add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE data AS
WITH t( ID, label, value, app_id ) AS
(
SELECT 120, 'A', 'Alpha', 1 FROM dual UNION ALL
SELECT 120, 'B', 'Beta' , 1 FROM dual UNION ALL
SELECT 120, 'C', 'Class', 1 FROM dual UNION ALL
SELECT 120, 'D', 'Delta', 1 FROM dual UNION ALL
SELECT 120, 'C', 'Alpha', 2 FROM dual UNION ALL
SELECT 120, 'D', 'Beta' , 2 FROM dual UNION ALL
SELECT 120, 'E', 'Class', 2 FROM dual UNION ALL
SELECT 120, 'F', 'Delta', 2 FROM dual
)
SELECT *
FROM t
8 rows affected
-- just swapped SYS_REFCURSOR with VARCHAR2 as returning data type for demonstration
CREATE OR REPLACE FUNCTION Get_Pivoted_Labels( i_id data.id%type, i_app_id data.app_id%type )
RETURN VARCHAR2 IS -- SYS_REFCURSOR IS
v_recordset SYS_REFCURSOR;
v_sql VARCHAR2(32767);
v_cols VARCHAR2(32767);
BEGIN
SELECT LISTAGG( ''''||label||''' AS "'||label||'"' , ',' )
WITHIN GROUP ( ORDER BY label )
INTO v_cols
FROM ( SELECT DISTINCT label
FROM data
WHERE ID = i_id AND app_id = i_app_id );

v_sql :=
'SELECT *
FROM data
PIVOT
(
MAX(value) FOR label IN ( '|| v_cols ||' )
)
WHERE ID = :id AND app_id = :aid';

OPEN v_recordset FOR v_sql USING i_id, i_app_id;
RETURN v_sql; -- v_recordset;
END;
/
BEGIN
DBMS_OUTPUT.PUT_LINE( Get_Pivoted_Labels(120,1) );
DBMS_OUTPUT.PUT_LINE( Get_Pivoted_Labels(120,2) );
END;
/
1 rows affected

dbms_output:
SELECT * 
         FROM data
        PIVOT 
        (
          MAX(value) FOR label IN ( 'A' AS "A",'B' AS "B",'C' AS "C",'D' AS "D" )
        )
       WHERE ID = :id AND app_id = :aid
SELECT * 
         FROM data
        PIVOT 
        (
          MAX(value) FOR label IN ( 'C' AS "C",'D' AS "D",'E' AS "E",'F' AS "F" )
        )
       WHERE ID = :id AND app_id = :aid
-- copied from the above generated queries
SELECT *
FROM data
PIVOT
(
MAX(value) FOR label IN ('A' AS "A", 'B' AS "B",'C' AS "C",'D' AS "D")
)
WHERE ID = 120 AND app_id = 1
ID APP_ID A B C D
120 1 Alpha Beta Class Delta
SELECT *
FROM data
PIVOT
(
MAX(value) FOR label IN ('C' AS "C",'D' AS "D",'E' AS "E",'F' AS "F")
)
WHERE ID = 120 AND app_id = 2
ID APP_ID C D E F
120 2 Alpha Beta Class Delta