En ocasiones, es necesario agregar datos de varias filas en una sola fila, dando una lista de datos asociados con un valor específico. Utilizando la tabla SCOTT.EMP como ejemplo, podríamos querer recuperar una lista de empleados para cada departamento. A continuación se muestra una lista de los datos base y el tipo de resultado que nos gustaría obtener de una consulta agregada.
Base Data:
DEPTNO ENAME
---------- ----------
20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
10 CLARK
20 SCOTT
10 KING
30 TURNER
20 ADAMS
30 JAMES
20 FORD
10 MILLER
Desired Output:
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
Este artículo se basa en un hilo de asktom.oracle.com y contiene varios métodos para lograr los resultados deseados.
- Preparar
- Función Analítica LISTAGG en 11g Release 2
- Función incorporada WM_CONCAT (Not Supported)
- Función agregada definida por el usuario
- Función específica
- Función genérica usando Ref Cursor
- Funciones ROW_NUMBER() y SYS_CONNECT_BY_PATH en Oracle 9i
- Función COLLECT en Oracle 10g
Preparar
Los ejemplos de este artículo requieren la siguiente tabla.
--DROP TABLE emp PURGE;
CREATE TABLE emp (
empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
Función analítica LISTAGG en 11g versión 2
La función analítica LISTAGG se introdujo en Oracle 11g Release 2, lo que facilita la agregación de cadenas. Lo bueno de esta función es que también nos permite ordenar los elementos en la lista concatenada. Si está utilizando 11g Release 2, debería utilizar esta función para la agregación de cadenas.
COLUMN employees FORMAT A50
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
3 rows selected.
SQL>
Función incorporada WM_CONCAT (Not Supported)
Si no está ejecutando 11g Release 2 o superior, pero está ejecutando una versión de la base de datos donde está presente la función WM_CONCAT, entonces es una solución de esfuerzo cero, ya que realiza la agregación por usted. En realidad, es un ejemplo de una función agregada definida por el usuario que se describe a continuación, pero Oracle ha hecho todo el trabajo por usted.
COLUMN employees FORMAT A50
SELECT deptno, wm_concat(ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
3 rows selected.
SQL>
WM_CONCAT es una función no documentada y, como tal, Oracle no admite aplicaciones de usuario (MOS Note ID 1336219.1). Si esto le preocupa, utilice una función agregada definida por el usuario que se describe a continuación.
Además, WM_CONCAT se ha eliminado de 12c en adelante, por lo que no puede elegir esta opción.
Función agregada definida por el usuario
La función WM_CONCAT descrita anteriormente es un ejemplo de una función agregada definida por el usuario que Oracle ya ha creado para usted. Si no desea utilizar WM_CONCAT, puede crear su propia función agregada definida por el usuario como se describe en asktom.oracle.com. Gracias a Kim Berg Hansen por algunas correcciones en los comentarios.
CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
g_string VARCHAR2(32767),
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
value IN VARCHAR2 )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_string_agg)
RETURN NUMBER
);
/
SHOW ERRORS
CREATE OR REPLACE TYPE BODY t_string_agg IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
RETURN NUMBER IS
BEGIN
sctx := t_string_agg(NULL);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
value IN VARCHAR2 )
RETURN NUMBER IS
BEGIN
SELF.g_string := self.g_string || ',' || value;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER IS
BEGIN
returnValue := SUBSTR(SELF.g_string, 2);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_string_agg)
RETURN NUMBER IS
BEGIN
SELF.g_string := SELF.g_string || ctx2.g_string;
RETURN ODCIConst.Success;
END;
END;
/
SHOW ERRORS
CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS
La función agregada se implementa mediante un tipo y un cuerpo de tipo, y se utiliza dentro de una consulta.
COLUMN employees FORMAT A50
SELECT /*+ PARALLEL(2) */ deptno, string_agg(ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
3 rows selected.
SQL>
Función específica
Un enfoque es escribir una función específica para resolver los problemas. La función get_employees que se muestra a continuación devuelve una lista de empleados para el departamento especificado.
CREATE OR REPLACE FUNCTION get_employees (p_deptno in emp.deptno%TYPE)
RETURN VARCHAR2
IS
l_text VARCHAR2(32767) := NULL;
BEGIN
FOR cur_rec IN (SELECT ename FROM emp WHERE deptno = p_deptno) LOOP
l_text := l_text || ',' || cur_rec.ename;
END LOOP;
RETURN LTRIM(l_text, ',');
END;
/
SHOW ERRORS
Luego, la función se puede incorporar a una consulta de la siguiente manera.
COLUMN employees FORMAT A50
SELECT deptno,
get_employees(deptno) AS employees
FROM emp
GROUP by deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
3 rows selected.
SQL>
Para reducir el número de llamadas a la función y, por lo tanto, mejorar el rendimiento, es posible que deseemos filtrar las filas por adelantado.
COLUMN employees FORMAT A50
SELECT e.deptno,
get_employees(e.deptno) AS employees
FROM (SELECT DISTINCT deptno
FROM emp) e;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
3 rows selected.
SQL>
Función genérica usando Ref Cursor
Un enfoque alternativo es escribir una función para concatenar los valores pasados usando un cursor de referencia. Esto es esencialmente lo mismo que el ejemplo anterior, excepto que el cursor se pasa para hacerlo genérico, como se muestra a continuación.
CREATE OR REPLACE FUNCTION concatenate_list (p_cursor IN SYS_REFCURSOR)
RETURN VARCHAR2
IS
l_return VARCHAR2(32767);
l_temp VARCHAR2(32767);
BEGIN
LOOP
FETCH p_cursor
INTO l_temp;
EXIT WHEN p_cursor%NOTFOUND;
l_return := l_return || ',' || l_temp;
END LOOP;
RETURN LTRIM(l_return, ',');
END;
/
SHOW ERRORS
La expresión CURSOR se usa para permitir que se pase una consulta a la función como un cursor de referencia, como se muestra a continuación.
COLUMN employees FORMAT A50
SELECT e1.deptno,
concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE e2.deptno = e1.deptno)) employees
FROM emp e1
GROUP BY e1.deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
3 rows selected.
SQL>
Una vez más, el número total de llamadas a funciones se puede reducir filtrando los distintos valores, en lugar de llamar a la función para cada fila.
COLUMN employees FORMAT A50
SELECT deptno,
concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE e2.deptno = e1.deptno)) employees
FROM (SELECT DISTINCT deptno
FROM emp) e1;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
3 rows selected.
SQL>
Funciones ROW_NUMBER () y SYS_CONNECT_BY_PATH en Oracle 9i
Un ejemplo en williamrobertson.net usa las funciones ROW_NUMBER () y SYS_CONNECT_BY_PATH para lograr el mismo resultado sin el uso de PL/SQL o definiciones de tipo adicionales.
SELECT deptno,
LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM (SELECT deptno,
ename,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
FROM emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
3 rows selected.
SQL>
Función COLLECT en Oracle 10g
Un ejemplo en oracle-developer.net usa la función COLLECT en Oracle 10g para obtener el mismo resultado. Este método requiere un tipo de tabla y una función para convertir el contenido del tipo de tabla en una cadena. He modificado ligeramente su método para adaptarlo a este artículo.
CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab IN t_varchar2_tab,
p_delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
l_string VARCHAR2(32767);
BEGIN
FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
IF i != p_varchar2_tab.FIRST THEN
l_string := l_string || p_delimiter;
END IF;
l_string := l_string || p_varchar2_tab(i);
END LOOP;
RETURN l_string;
END tab_to_string;
/
La consulta siguiente muestra la función COLLECT en acción.
COLUMN employees FORMAT A50
SELECT deptno,
tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
3 rows selected.
SQL>
Para más información, ver:
• User-Defined Aggregate Functions
• asktom.oracle.com
• williamrobertson.net
• oracle-developer.net – the collect function in 10g
• LISTAGG
