Come convertire valori separati da virgola a righe in oracle?

Ecco il DDL –

create table tbl1 ( id number, value varchar2(50) ); insert into tbl1 values (1, 'AA, UT, BT, SK, SX'); insert into tbl1 values (2, 'AA, UT, SX'); insert into tbl1 values (3, 'UT, SK, SX, ZF'); 

Nota, qui il valore è una stringa separata da virgole .

Ma abbiamo bisogno di risultati come segue-

 ID VALUE ------------- 1 AA 1 UT 1 BT 1 SK 1 SX 2 AA 2 UT 2 SX 3 UT 3 SK 3 SX 3 ZF 

Come scriviamo SQL per questo?

Sono d’accordo che si tratta di un design davvero pessimo. Prova questo se non riesci a cambiare quel disegno:

 select distinct id, trim(regexp_substr(value,'[^,]+', 1, level) ) value, level from tbl1 connect by regexp_substr(value, '[^,]+', 1, level) is not null order by id, level; 

USCITA

 id value level 1 AA 1 1 UT 2 1 BT 3 1 SK 4 1 SX 5 2 AA 1 2 UT 2 2 SX 3 3 UT 1 3 SK 2 3 SX 3 3 ZF 4 

Crediti a questo

Per rimuovere i duplicati in un modo più elegante ed efficiente (crediti a @mathguy)

 select id, trim(regexp_substr(value,'[^,]+', 1, level) ) value, level from tbl1 connect by regexp_substr(value, '[^,]+', 1, level) is not null and PRIOR id = id and PRIOR SYS_GUID() is not null order by id, level; 

Se vuoi un approccio “ANSIer” vai con un CTE:

 with t (id,res,val,lev) as ( select id, trim(regexp_substr(value,'[^,]+', 1, 1 )) res, value as val, 1 as lev from tbl1 where regexp_substr(value, '[^,]+', 1, 1) is not null union all select id, trim(regexp_substr(val,'[^,]+', 1, lev+1) ) res, val, lev+1 as lev from t where regexp_substr(val, '[^,]+', 1, lev+1) is not null ) select id, res,lev from t order by id, lev; 

PRODUZIONE

 id val lev 1 AA 1 1 UT 2 1 BT 3 1 SK 4 1 SX 5 2 AA 1 2 UT 2 2 SX 3 3 UT 1 3 SK 2 3 SX 3 3 ZF 4 

Un altro approccio ricorsivo da parte di MT0 ma senza regex:

 WITH t ( id, value, start_pos, end_pos ) AS ( SELECT id, value, 1, INSTR( value, ',' ) FROM tbl1 UNION ALL SELECT id, value, end_pos + 1, INSTR( value, ',', end_pos + 1 ) FROM t WHERE end_pos > 0 ) SELECT id, SUBSTR( value, start_pos, DECODE( end_pos, 0, LENGTH( value ) + 1, end_pos ) - start_pos ) AS value FROM t ORDER BY id, start_pos; 

Ho provato 3 approcci con un set di dati di 30000 righe e 118104 righe restituite e ho ottenuto i seguenti risultati medi:

  • Il mio approccio ricorsivo: 5 secondi
  • Approccio MT0: 4 secondi
  • Approccio di Mathguy: 16 secondi
  • Approccio ricorsivo MT0 senza regex: 3,45 secondi

@ Mathath ha anche testato con un set di dati più grande:

In tutti i casi la query ricorsiva (ho provato solo quello con substr e instr normali) fa meglio, con un fattore da 2 a 5. Ecco le combinazioni di # di stringhe / token per stringa e tempi di esecuzione di CTAS per gerarchico vs. ricorsivo prima gerarchico. Tutte le volte in secondi

  • 30.000 x 4: 5/1.
  • 30.000 x 10: 15/3.
  • 30.000 x 25: 56/37.
  • 5.000 x 50: 33/14.
  • 5.000 x 100: 160/81.
  • 10.000 x 200: 1.924 / 772

Ciò otterrà i valori senza che sia necessario rimuovere i duplicati o utilizzare un trucco per includere SYS_GUID() o DBMS_RANDOM.VALUE() in CONNECT BY :

 SELECT t.id, v.COLUMN_VALUE AS value FROM TBL1 t, TABLE( CAST( MULTISET( SELECT TRIM( REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL ) ) FROM DUAL CONNECT BY LEVEL < = REGEXP_COUNT( t.value, '[^,]+' ) ) AS SYS.ODCIVARCHAR2LIST ) ) v 

Aggiornamento :

Restituisce l'indice dell'elemento nell'elenco:

Opzione 1 - Restituzione di un UDT:

 CREATE TYPE string_pair IS OBJECT( lvl INT, value VARCHAR2(4000) ); / CREATE TYPE string_pair_table IS TABLE OF string_pair; / SELECT t.id, v.* FROM TBL1 t, TABLE( CAST( MULTISET( SELECT string_pair( level, TRIM( REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL ) ) ) FROM DUAL CONNECT BY LEVEL < = REGEXP_COUNT( t.value, '[^,]+' ) ) AS string_pair_table ) ) v; 

Opzione 2: utilizza ROW_NUMBER() :

 SELECT t.id, v.COLUMN_VALUE AS value, ROW_NUMBER() OVER ( PARTITION BY id ORDER BY ROWNUM ) AS lvl FROM TBL1 t, TABLE( CAST( MULTISET( SELECT TRIM( REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL ) ) FROM DUAL CONNECT BY LEVEL < = REGEXP_COUNT( t.value, '[^,]+' ) ) AS SYS.ODCIVARCHAR2LIST ) ) v; 

Vercelli ha pubblicato una risposta corretta. Tuttavia, con più di una stringa da dividere, connect by genererà un numero di righe in crescita esponenziale, con molti, molti duplicati. (Basta provare la query senza distinct .) Questo distruggerà le prestazioni su dati di dimensioni non banali.

Un modo comune per superare questo problema è utilizzare una condizione prior e un controllo aggiuntivo per evitare cicli nella gerarchia. Così:

 select id, trim(regexp_substr(value,'[^,]+', 1, level) ) value, level from tbl1 connect by regexp_substr(value, '[^,]+', 1, level) is not null and prior id = id and prior sys_guid() is not null order by id, level; 

Vedi, per esempio, questa discussione su OTN: https://community.oracle.com/thread/2526535

Un metodo alternativo consiste nel definire una semplice funzione PL / SQL:

 CREATE OR REPLACE FUNCTION split_String( i_str IN VARCHAR2, i_delim IN VARCHAR2 DEFAULT ',' ) RETURN SYS.ODCIVARCHAR2LIST DETERMINISTIC AS p_result SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(); p_start NUMBER(5) := 1; p_end NUMBER(5); c_len CONSTANT NUMBER(5) := LENGTH( i_str ); c_ld CONSTANT NUMBER(5) := LENGTH( i_delim ); BEGIN IF c_len > 0 THEN p_end := INSTR( i_str, i_delim, p_start ); WHILE p_end > 0 LOOP p_result.EXTEND; p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start ); p_start := p_end + c_ld; p_end := INSTR( i_str, i_delim, p_start ); END LOOP; IF p_start < = c_len + 1 THEN p_result.EXTEND; p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 ); END IF; END IF; RETURN p_result; END; / 

Quindi l'SQL diventa molto semplice:

 SELECT t.id, v.column_value AS value FROM TBL1 t, TABLE( split_String( t.value ) ) v