Utilidades SQL - Convertir datos delimitados a filas

Saludos,

Supongamos que tenemos una tabla con un campo que almacena una lista de datos separados por algun delimitador, como podemos convertir esa cadena en filas?

Aunque no es lo recomendado almacenar datos concatenados en un campo, sino utilizar tablas detalle, a veces nos topamos con este escenario.

Ejemplo de una cadena que tiene concatenado 3 codigos de almacen. A continuación 3 opciones para usar:

 XMLTABLE

SQL>SELECT TRIM (COLUMN_VALUE) CODIGO FROM XMLTABLE (REPLACE ('"' || 'ALM01,ALM02,ALM03' || '"', ',', '","')); CODIGO -------------------------------------------------------------------------------- ALM01 ALM02 ALM03

REGEXP_SUBSTR/CONNECT BY

SQL>SELECT REGEXP_SUBSTR('ALM01,ALM02,ALM03','[^,]+',1,LEVEL) CODIGO FROM DUAL CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('ALM01,ALM02,ALM03','[^,]+')) + 1; CODIGO -------------------------------------------------------------------------------- ALM01 ALM02 ALM03

 
 
SYS.DBMS_DEBUG_VC2COLL

SQL>SELECT COLUMN_VALUE FROM TABLE(SYS.DBMS_DEBUG_VC2COLL('ALM01', 'ALM02', 'ALM03')); CODIGO -------------------------------------------------------------------------------- ALM01 ALM02 ALM03

Nota: SYS.DBMS_DEBUG_VC2COLL No recibe cadena concatenada, solo se pasa como parametros cada texto que deseamo convertir a filas.

Ahora veamos un ejemplo mas, supogamos que tenemos una tabla con los nombres de vendedores y en el se encuentra los dias libres, ahora queremos geenrar un calendario semana de dias habiles vs laborables.

-------------------------------------------------------------------------------- CREATE TABLE VENDEDOR ( COD_VENDEDOR VARCHAR2(5), NOMBRE VARCHAR2(200), DIAS_LIBRES VARCHAR2(200), PRIMARY KEY (COD_VENDEDOR) ); INSERT INTO VENDEDOR(COD_VENDEDOR,NOMBRE,DIAS_LIBRES) VALUES('001','VICTOR','LUNES,MARTES'); INSERT INTO VENDEDOR(COD_VENDEDOR,NOMBRE,DIAS_LIBRES) VALUES('002','MANUEL','MIERCOLES,JUEVES'); INSERT INTO VENDEDOR(COD_VENDEDOR,NOMBRE,DIAS_LIBRES) VALUES('003','RAFAEL','DOMINGO,LUNES');

En este ejemplo usar XMLTABLE para transforma la cadena separada por "," a filas

-------------------------------------------------------------------------------- SELECT V.*, TRIM (COLUMN_VALUE) DIAS FROM VENDEDOR v, XMLTABLE (REPLACE ('"' ||V.DIAS_LIBRES|| '"', ',', '","'));

Listo, como ven del campo DIAS_LIBRES se descompone a filas representado en el campo dias.

Ahora  nos queda presntarlo como tabla en donde las filas son los vendedores y las columnas los dias, la interseccion de estos representaran en 1=LIBRE y 0=Laborable. Para esto nos apoyaremos del comando PIVOT.

-------------------------------------------------------------------------------- SELECT * FROM     (  SELECT V.*, TRIM (COLUMN_VALUE) DIAS FROM VENDEDOR V,XMLTABLE (REPLACE ('"' ||V.DIAS_LIBRES|| '"', ',', '","'))) PIVOT (COUNT(COD_VENDEDOR) FOR DIAS IN ('LUNES', 'MARTES', 'MIERCOLES','JUEVES','VIERNES','SABADO','DOMINGO'))

Listo, ya tenemos nuestro calendario de dias libres!!