¿Cómo convertir valores separados por comas en filas en Oracle?

Aquí está el 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 aquí el valor es una cadena separada por comas .

Pero necesitamos resultados como los siguientes:

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

¿Cómo escribimos SQL para esto?

Jul 14 '16 17:07 Samiul Al Hossaini

Estoy de acuerdo en que este es un diseño realmente malo. Prueba esto si no puedes cambiar ese diseño:

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;

SALIDA

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 esto

Para eliminar duplicados de una manera más elegante y eficiente (créditos 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;

Si desea un enfoque "ANSIer", opte por 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;

PRODUCCIÓN

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

Otro enfoque recursivo de MT0 pero sin expresiones regulares:

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;

Probé 3 enfoques con un conjunto de datos de 30000 filas y devolví 118104 filas y obtuve los siguientes resultados promedio:

Mi enfoque recursivo: 5 segundos
Aproximación MT0: 4 segundos
Enfoque de Mathguy: 16 segundos
Enfoque recursivo MT0 sin expresiones regulares: 3,45 segundos

@Mathguy también ha probado con un conjunto de datos más grande:

En todos los casos, la consulta recursiva (solo probé la que tiene substr e instr regulares) funciona mejor, por un factor de 2 a 5. Aquí están las combinaciones de número de cadenas/tokens por cadena y tiempos de ejecución de CTAS para jerárquico versus recursivo. , jerárquico primero. Todos los tiempos en segundos

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

Jul 14 '2016 10:07 vercelli

Esto obtendrá los valores sin necesidad de eliminar duplicados o tener que usar un truco para incluir SYS_GUID()o DBMS_RANDOM.VALUE()en 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

Actualizar :

Devolviendo el índice del elemento en la lista:

Opción 1: devolver 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;

Opción 2 - Uso 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;

Jul 14 '2016 11:07 MT0

Vercelli publicó una respuesta correcta. Sin embargo, con más de una cadena para dividir, connect byse generará un número de filas que crece exponencialmente, con muchos, muchos duplicados. (Simplemente intente realizar la consulta sin distinct). Esto destruirá el rendimiento de datos de tamaño no trivial.

Una forma común de superar este problema es utilizar una priorcondición y una verificación adicional para evitar ciclos en la jerarquía. Al igual que:

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;

Consulte, por ejemplo, esta discusión sobre OTN: https://community.oracle.com/thread/2526535

Jul 14 '2016 11:07