No me gusta mucho la empresa Oracle, ni su base de datos, pero hay que reconocer que tiene cosas fuera de serie. Ojo esta funcionalidad no es ninguna novedad es de 10g; pero es una novedad para mi. :P
Explico el problema, en ciertos casos tenemos que mostrar datos agrupados pero estos se pueden repetir, por lo tanto generan una nueva fila y nosotros no queremos esto. Los complique! Vamos con un ejemplo que es más fácil tengo una tabla persona y esta persona tiene mails, uno o muchos. Quiero mostrar la persona y sus mails pero siempre tiene que haber una linea por persona. Ahora si?
En Oracle existe collect esta función nos permite agrupar datos, por ejemplo:
SELECT p.nombre
, COLLECT(m.mail) AS mails
FROM persona p, mails m
WHERE p.id = m.persona_id
GROUP BY p.nombre;
Con esta sencilla query tengo la lista de mail por nombre de persona. Ahora quiero los mails que no se repiten:
SELECT p.nombre
, COLLECT( DISTINCT m.mail) AS mails
FROM persona p, mails m
WHERE p.id = m.persona_id
GROUP BY p.nombre;
Si lo ejecutamos vamos a ver que no se muestra muy lindo, lo que podemos hacer es hacer un tipo y castearlo a ese tipo:
CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
SELECT p.nombre
, CAST(COLLECT( DISTINCT m.mail) AS varchar2_ntt) AS mails
FROM persona p, mails m
WHERE p.id = m.persona_id
GROUP BY p.nombre;
Bueno ahora se ve mejor pero no esta de diez. Si queremos que quede pippicucu tenemos que hacer uns función. Es raro que oracle no proponga o tenga ninguna:
CREATE FUNCTION to_string (
nt_in IN varchar2_ntt,
delimiter_in IN VARCHAR2 DEFAULT ','
) RETURN VARCHAR2 IS
v_idx PLS_INTEGER;
v_str VARCHAR2(32767);
v_dlm VARCHAR2(10);
BEGIN
v_idx := nt_in.FIRST;
WHILE v_idx IS NOT NULL LOOP
v_str := v_str || v_dlm || nt_in(v_idx);
v_dlm := delimiter_in;
v_idx := nt_in.NEXT(v_idx);
END LOOP;
RETURN v_str;
END to_string;
/
Con esta función es todo más fácil:
SELECT p.nombre
, to_string(CAST(COLLECT( DISTINCT m.mail) AS varchar2_ntt)) AS mails
FROM persona p, mails m
WHERE p.id = m.persona_id
GROUP BY p.nombre;
Y si queremos que quede más cheto agregamos el separador como ', '
SELECT p.nombre
, to_string(CAST(COLLECT( DISTINCT m.mail) AS varchar2_ntt), ', ' ) AS mails
FROM persona p, mails m
WHERE p.id = m.persona_id
GROUP BY p.nombre;
La verdad muy útil!!
Dejo link:
http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions031.htm#i1271564
http://www.oracle-developer.net/display.php?id=306
