ORACLE SQL – CONVERTER DINAMICAMENTE FLAT COMMA EM COLUNAS

WITH bounds ( id, list, start_pos, end_pos, lvl ) AS (
SELECT ROWNUM,
cols,
1,
INSTR( cols, ',' ),
1
FROM   ( SELECT 'col1' cols FROM DUAL UNION ALL
SELECT 'val1,val2,val3,val4' cols FROM DUAL UNION ALL
SELECT 'valA,valB,valC' cols FROM DUAL) data
UNION ALL
SELECT id,
list,
end_pos + 1,
INSTR( list, ',', end_pos + 1 ),
lvl + 1
FROM   bounds
WHERE  end_pos > 0
),
items ( id, item, col ) AS (
SELECT id,
SUBSTR(
list,
start_pos,
DECODE( end_pos, 0, LENGTH( list ) + 1, end_pos ) - start_pos
),
lvl
FROM   bounds
)
SELECT *
FROM   items
PIVOT  (
MAX( item ) FOR col IN (
1 AS col1,
2 AS col2,
3 AS col3,
4 AS col4
)
)

WITH bounds ( id, list, start_pos, end_pos, lvl ) AS (
SELECT ROWNUM,
cols,
1,
INSTR( cols, ',' ),
1
FROM   ( SELECT 'col1' cols FROM DUAL UNION ALL
SELECT 'val1,val2,val3,val4' cols FROM DUAL UNION ALL
SELECT 'valA,valB,valC'  cols FROM DUAL
) data
UNION ALL
SELECT id,
list,
end_pos + 1,
INSTR( list, ',', end_pos + 1 ),
lvl + 1
FROM   bounds
WHERE  end_pos > 0
)
SELECT id,
SUBSTR(
list,
start_pos,
DECODE( end_pos, 0, LENGTH( list ) + 1, end_pos ) - start_pos
) AS item,
lvl,
MAX( lvl ) OVER () AS num_columns
FROM   bounds
ORDER BY id, lvl




CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE
FUNCTION cto_table(p_sep in Varchar2, p_list IN VARCHAR2)
  RETURN t_my_list
AS
  l_string VARCHAR2(32767) := p_list || p_sep;
  l_sep_index PLS_INTEGER;
  l_index PLS_INTEGER := 1;
  l_tab t_my_list     := t_my_list();
BEGIN
  LOOP
    l_sep_index := INSTR(l_string, p_sep, l_index);
    EXIT
  WHEN l_sep_index = 0;
    l_tab.EXTEND;
    l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_sep_index - l_index));
    l_index            := l_sep_index + 1;
  END LOOP;
  RETURN l_tab;
END cto_table;


select * from (
  select rownum r , collection.*  
    from TABLE(cto_table(',','1.25, 3.87, 2, 19,, 1, 9, ')) collection
)
PIVOT ( 
  LISTAGG(column_value) within group (order by 1) as val 
  for r in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
)