Oracle 11g Release 2

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
ID	NAME	t1	t2	t3
1	n1	v1	v2	null
2	n2	v1	v5	v3