Como converter valores separados por vírgulas em linhas no oracle?

Aqui está o 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'); 

Observe que o valor aqui é uma string separada por vírgulas .

Mas precisamos de resultado como seguir

 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 

Como escrevemos SQL para isso?

   

Eu concordo que este é um projeto muito ruim. Tente isso se você não puder alterar esse design:

 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; 

OUPUT

 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 

Créditos a este

Para remover duplicatas de uma maneira mais elegante e eficiente (créditos para @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 você quiser uma abordagem “ANSIer”, use um 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; 

SAÍDA

 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 

Outra abordagem recursiva por MT0 mas sem 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; 

Eu tentei 3 abordagens com um dataset de 30000 linhas e 118104 linhas retornadas e obtive os seguintes resultados médios:

  • Minha abordagem recursiva: 5 segundos
  • Abordagem MT0: 4 segundos
  • Abordagem Mathguy: 16 segundos
  • Abordagem recursiva MT0 no-regex: 3.45 segundos

@Mathguy também testou com um dataset maior:

Em todos os casos, a consulta recursiva (eu testei somente aquela com substr e instr regulares) é melhor, por um fator de 2 a 5. Aqui estão as combinações de # de strings / tokens por string e tempos de execução de CTAS para hierárquico vs. recursivo primeiro hierárquico. Todos os tempos em segundos

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

Isso obterá os valores sem exigir que você remova duplicatas ou tenha que usar um hack de include SYS_GUID() ou DBMS_RANDOM.VALUE() no 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 

Atualização :

Retornando o índice do elemento na lista:

Opção 1 - Retornar um 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; 

Opção 2 - usar 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 postou uma resposta correta. No entanto, com mais de uma cadeia para dividir, connect by gerará um número de linhas exponencialmente crescente, com muitas, muitas duplicatas. (Apenas tente a consulta sem distinct .) Isso destruirá o desempenho em dados de tamanho não trivial.

Uma maneira comum de superar esse problema é usar uma condição prior e uma verificação adicional para evitar ciclos na hierarquia. Igual a:

 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; 

Veja, por exemplo, esta discussão sobre OTN: https://community.oracle.com/thread/2526535

Um método alternativo é definir uma function simples de 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; / 

Então o SQL se torna muito simples:

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