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 table1 AS
WITH t( id, name ) AS
(
SELECT 1, 'n1' FROM dual UNION ALL
SELECT 2, 'n2' FROM dual
)
SELECT * FROM t
2 rows affected
CREATE TABLE table2 AS
WITH t( id, tipo, valor ) AS
(
SELECT 1, 't1', 'v1' FROM dual UNION ALL
SELECT 1, 't2', 'v2' FROM dual UNION ALL
SELECT 2, 't1', 'v1' FROM dual UNION ALL
SELECT 2, 't2', 'v5' FROM dual UNION ALL
SELECT 2, 't3', 'v3' FROM dual
)
SELECT * FROM t
5 rows affected
--just replaced the returning value's type from SYS_REFCURSOR to VARCHAR2 in order to
--show the generated SQL statement
CREATE OR REPLACE FUNCTION Get_Pivoted_Cols RETURN VARCHAR2 IS
v_sql VARCHAR2(32767);
v_cols VARCHAR2(32767);
BEGIN
SELECT LISTAGG( ''''||tipo||''' AS "'||tipo||'"' , ',' )
WITHIN GROUP ( ORDER BY tipo )
INTO v_cols
FROM ( SELECT DISTINCT tipo FROM table2 );

v_sql := 'SELECT *
FROM (SELECT t1.*, t2.tipo, t2.valor
FROM table1 t1
JOIN table2 t2
ON t2.id = t1.id)
PIVOT
(
MAX(valor) FOR tipo IN ( '|| v_cols ||' )
)';
RETURN v_sql;
END;
/
DECLARE
v_sql VARCHAR2(32767);
BEGIN
v_sql := Get_Pivoted_Cols;
DBMS_OUTPUT.PUT_LINE(v_sql);
END;
/
1 rows affected

dbms_output:
SELECT *
              FROM (SELECT t1.*, t2.tipo, t2.valor 
                      FROM table1 t1 
                      JOIN table2 t2 
                        ON t2.id = t1.id)
             PIVOT 
             (
              MAX(valor) FOR tipo IN ( 't1' AS "t1",'t2' AS "t2",'t3' AS "t3" )
             )
SELECT *
FROM (SELECT t1.*, t2.tipo, t2.valor
FROM table1 t1
JOIN table2 t2
ON t2.id = t1.id)
PIVOT
(
MAX(valor) FOR tipo IN ( 't1' AS "t1", 't2' AS "t2", 't3' AS "t3" )
)
ID NAME t1 t2 t3
1 n1 v1 v2 null
2 n2 v1 v5 v3