ORACLE: Agrupar datos de varias filas a una lista en una sola columna
Por amunoz el Lunes, 3 de Noviembre de 2014, 20:35 - Enlace permanente
Alguna vez hemos tenido que pasar datos que tenemos en varias filas a un sólo campo, esto es trivial según la versión de Oracle que usemos...
Imaginemos que tenemos la siguiente tabla:
CREATE TABLE USUARIO_PAIS (
ID_USUARIO NUMBER(10),
ID_PAIS NUMBER(10)
);
con los siguientes datos:
ID_USUARIO ID_PAIS
1 10
2 10
3 12
4 12
5 11
Si queremos agrupar los usuarios por países en Oracle 11g haremos así:
SELECT id_pais, LISTAGG (id_usuario, ',') WITHIN GROUP (ORDER BY id_usuario) usuarios FROM USUARIO_PAIS GROUP BY id_pais
y el resultado será el siguiente:
ID_PAIS USUARIOS
10 1,2
11 5
12 3,4
La función de agregado LISTAGG no existe en versiones anteriores a Oracle 11g, pero podemos solucionarlo creando nuestra propia función de agregado. Nuestra función se apoyará en un tipo definido de tipo varchar (si agrupamos tipos de datos distintos a varchar como es el caso tendremos que convertirlos a varchar).
CREATE OR REPLACE TYPE T_LISTA_TAB AS TABLE OF VARCHAR2 (4000);
CREATE OR REPLACE FUNCTION LISTAVARCHAR (p_lista_tab IN T_LISTA_TAB, p_separador IN VARCHAR2 DEFAULT ',' )
RETURN VARCHAR2
IS
resultado VARCHAR2 (32767);
BEGIN
IF p_lista_tab.COUNT > 0
THEN
FOR i IN p_lista_tab.FIRST .. p_lista_tab.LAST
LOOP
IF i != p_lista_tab.FIRST
THEN
resultado := resultado || p_separador;
END IF;
resultado := resultado || p_lista_tab (i);
END LOOP;
END IF;
RETURN resultado;
END LISTAVARCHAR;
/
Ahora cambiamos la consulta de la siguiente manera para usar nuestra función:
SELECT id_pais, LISTAVARCHAR(CAST(COLLECT(TO_CHAR(id_usuario) ORDER BY id_usuario) AS T_LISTA_TAB), ',') usuarios FROM USUARIO_PAIS GROUP BY id_pais
La función COLLECT nos crea un "DataSet" con los datos agrupados, al cual le hacemos un cast a nuestro tipo. y la función se encarga de recorrer cada tabla y concatenarla según el separador.
Y el resultado será el mismo.
Saludos!