lunes, 4 de diciembre de 2017

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!!


No hay comentarios:

Publicar un comentario