tabla - string_agg sql server 2014
LISTAGG en oráculo para devolver valores distintos (19)
¿Alguien ha pensado en usar una cláusula PARTITION BY? Me funcionó en esta consulta obtener una lista de servicios de aplicaciones y el acceso.
SELECT DISTINCT T.APP_SVC_ID,
LISTAGG(RTRIM(T.ACCESS_MODE), '','') WITHIN GROUP(ORDER BY T.ACCESS_MODE) OVER(PARTITION BY T.APP_SVC_ID) AS ACCESS_MODE
FROM APP_SVC_ACCESS_CNTL T
GROUP BY T.ACCESS_MODE, T.APP_SVC_ID
Tuve que cortar mi cláusula where para NDA, pero entiendes la idea.
Estoy tratando de usar la función LISTAGG en Oracle. Me gustaría obtener solo los valores distintos para esa columna. ¿Hay alguna manera de obtener solo los valores distintos sin crear una función o un procedimiento?
col1 col2 Created_by 1 2 Smith 1 2 John 1 3 Ajay 1 4 Ram 1 5 Jack
Necesito seleccionar col1 y LISTAGG de col2 (columna 3 no se considera). Cuando hago eso, obtengo algo así como resultado de LISTAGG : [2,2,3,4,5]
Necesito eliminar el duplicado ''2'' aquí; Necesito solo los valores distintos de col2 contra col1.
¿Qué pasa con la creación de una función dedicada que hará que la parte "distinta":
create or replace function listagg_distinct (t in str_t, sep IN VARCHAR2 DEFAULT '','')
return VARCHAR2
as
l_rc VARCHAR2(4096) := '''';
begin
SELECT listagg(val, sep) WITHIN GROUP (ORDER BY 1)
INTO l_rc
FROM (SELECT DISTINCT column_value val FROM table(t));
RETURN l_rc;
end;
/
Y luego úsala para hacer la agregación:
SELECT col1, listagg_distinct(cast(collect(col_2) as str_t ), '', '')
FROM your_table
GROUP BY col_1;
A continuación, le indicamos cómo solucionar su problema.
select
regexp_replace(
''2,2,2.1,3,3,3,3,4,4''
,''([^,]+)(,/1)*(,|$)'', ''/1/3'')
from dual
devoluciones
2,2,1,3,4
RESPUESTA (ver notas a continuación):
select col1,
regexp_replace(
listagg(
col2 , '','') within group (order by col2) -- sorted
,''([^,]+)(,/1)*(,|$)'', ''/1/3'') )
from tableX
where rn = 1
group by col1;
Nota: Lo anterior funcionará en la mayoría de los casos: la lista debe ordenarse, puede que tenga que recortar todo el espacio posterior y posterior dependiendo de sus datos.
Si tiene muchos elementos en un grupo> 20 o grandes tamaños de cadena, puede ejecutar el límite de cadena de oráculo ''el resultado de la concatenación de cadena es demasiado largo''. Así que ponga un número máximo en los miembros de cada grupo. Esto solo funcionará si está bien para enumerar solo los primeros miembros. Si tiene cadenas variables muy largas, es posible que esto no funcione. tendrás que experimentar
select col1,
case
when count(col2) < 100 then
regexp_replace(
listagg(col2, '','') within group (order by col2)
,''([^,]+)(,/1)*(,|$)'', ''/1/3'')
else
''Too many entries to list...''
end
from sometable
where rn = 1
group by col1;
Otra solución (no tan simple) para evitar el límite de tamaño de cadena oráculo: el tamaño de la cadena está limitado a 4000. Gracias a esta publicación here por user3465996
select col1 ,
dbms_xmlgen.convert( -- HTML decode
dbms_lob.substr( -- limit size to 4000 chars
ltrim( -- remove leading commas
REGEXP_REPLACE(REPLACE(
REPLACE(
XMLAGG(
XMLELEMENT("A",col2 )
ORDER BY col2).getClobVal(),
''<A>'','',''),
''</A>'',''''),''([^,]+)(,/1)*(,|$)'', ''/1/3''),
'',''), -- remove leading XML commas ltrim
4000,1) -- limit to 4000 string size
, 1) -- HTML.decode
as col2
from sometable
where rn = 1
group by col1;
algunos casos de prueba - FYI
regexp_replace(''2,2,2.1,3,3,4,4'',''([^,]+)(,/1)+'', ''/1'')
-> 2.1,3,4 Fail
regexp_replace(''2 ,2 ,2.1,3 ,3 ,4 ,4 '',''([^,]+)(,/1)+'', ''/1'')
-> 2 ,2.1,3,4 Success - fixed length items
elementos contenidos dentro de los artículos, por ejemplo. 2,21
regexp_replace(''2.1,1'',''([^,]+)(,/1)+'', ''/1'')
-> 2.1 Fail
regexp_replace(''2 ,2 ,2.1,1 ,3 ,4 ,4 '',''(^|,)(.+)(,/2)+'', ''/1/2'')
-> 2 ,2.1,1 ,3 ,4 -- success - NEW regex
regexp_replace(''a,b,b,b,b,c'',''(^|,)(.+)(,/2)+'', ''/1/2'')
-> a,b,b,c fail!
v3 - regex gracias Igor! funciona en todos los casos.
select
regexp_replace(''2,2,2.1,3,3,4,4'',''([^,]+)(,/1)*(,|$)'', ''/1/3'') ,
---> 2,2.1,3,4 works
regexp_replace(''2.1,1'',''([^,]+)(,/1)*(,|$)'', ''/1/3''),
--> 2.1,1 works
regexp_replace(''a,b,b,b,b,c'',''([^,]+)(,/1)*(,|$)'', ''/1/3'')
---> a,b,c works
from dual
Creo que esto podría ayudar - CASE las columnas tienen valor NULL si está duplicado - entonces no se agrega a la cadena LISTAGG:
with test_data as
(
select 1 as col1, 2 as col2, ''Smith'' as created_by from dual
union select 1, 2, ''John'' from dual
union select 1, 3, ''Ajay'' from dual
union select 1, 4, ''Ram'' from dual
union select 1, 5, ''Jack'' from dual
union select 2, 5, ''Smith'' from dual
union select 2, 6, ''John'' from dual
union select 2, 6, ''Ajay'' from dual
union select 2, 6, ''Ram'' from dual
union select 2, 7, ''Jack'' from dual
)
SELECT col1 ,
listagg(col2 , '','') within group (order by col2 ASC) AS orig_value,
listagg(CASE WHEN rwn=1 THEN col2 END , '','') within group (order by col2 ASC) AS distinct_value
from
(
select row_number() over (partition by col1,col2 order by 1) as rwn,
a.*
from test_data a
) a
GROUP BY col1
Resultados en:
COL1 ORIG DISTINCT
1 2,2,3,4,5 2,3,4,5
2 5,6,6,6,7 5,6,7
Escribí una función para manejar esto usando expresiones regulares. Los parámetros in son: 1) la llamada de listagg en sí misma 2) Una repetición del delimitador
create or replace function distinct_listagg
(listagg_in varchar2,
delimiter_in varchar2)
return varchar2
as
hold_result varchar2(4000);
begin
select rtrim( regexp_replace( (listagg_in)
, ''([^''||delimiter_in||'']*)(''||
delimiter_in||''/1)+($|''||delimiter_in||'')'', ''/1/3''), '','')
into hold_result
from dual;
return hold_result;
end;
Ahora no tiene que repetir la expresión regular cada vez que hace esto, simplemente diga:
select distinct_listagg(
listagg(myfield,'', '') within group (order by 1),
'', ''
)
from mytable;
Implementé esta función almacenada:
CREATE TYPE LISTAGG_DISTINCT_PARAMS AS OBJECT (ELEMENTO VARCHAR2(2000), SEPARATORE VARCHAR2(10));
CREATE TYPE T_LISTA_ELEMENTI AS TABLE OF VARCHAR2(2000);
CREATE TYPE T_LISTAGG_DISTINCT AS OBJECT (
LISTA_ELEMENTI T_LISTA_ELEMENTI,
SEPARATORE VARCHAR2(10),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LISTAGG_DISTINCT)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT T_LISTAGG_DISTINCT,
VALUE IN LISTAGG_DISTINCT_PARAMS )
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF IN T_LISTAGG_DISTINCT,
RETURN_VALUE OUT VARCHAR2,
FLAGS IN NUMBER )
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT T_LISTAGG_DISTINCT,
CTX2 IN T_LISTAGG_DISTINCT )
RETURN NUMBER
);
CREATE OR REPLACE TYPE BODY T_LISTAGG_DISTINCT IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LISTAGG_DISTINCT) RETURN NUMBER IS
BEGIN
SCTX := T_LISTAGG_DISTINCT(T_LISTA_ELEMENTI() , '','');
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LISTAGG_DISTINCT, VALUE IN LISTAGG_DISTINCT_PARAMS) RETURN NUMBER IS
BEGIN
IF VALUE.ELEMENTO IS NOT NULL THEN
SELF.LISTA_ELEMENTI.EXTEND;
SELF.LISTA_ELEMENTI(SELF.LISTA_ELEMENTI.LAST) := TO_CHAR(VALUE.ELEMENTO);
SELF.LISTA_ELEMENTI:= SELF.LISTA_ELEMENTI MULTISET UNION DISTINCT SELF.LISTA_ELEMENTI;
SELF.SEPARATORE := VALUE.SEPARATORE;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LISTAGG_DISTINCT, RETURN_VALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
STRINGA_OUTPUT CLOB:='''';
LISTA_OUTPUT T_LISTA_ELEMENTI;
TERMINATORE VARCHAR2(3):=''...'';
LUNGHEZZA_MAX NUMBER:=4000;
BEGIN
IF SELF.LISTA_ELEMENTI.EXISTS(1) THEN -- se esiste almeno un elemento nella lista
-- inizializza una nuova lista di appoggio
LISTA_OUTPUT := T_LISTA_ELEMENTI();
-- riversamento dei soli elementi in DISTINCT
LISTA_OUTPUT := SELF.LISTA_ELEMENTI MULTISET UNION DISTINCT SELF.LISTA_ELEMENTI;
-- ordinamento degli elementi
SELECT CAST(MULTISET(SELECT * FROM TABLE(LISTA_OUTPUT) ORDER BY 1 ) AS T_LISTA_ELEMENTI ) INTO LISTA_OUTPUT FROM DUAL;
-- concatenazione in una stringa
FOR I IN LISTA_OUTPUT.FIRST .. LISTA_OUTPUT.LAST - 1
LOOP
STRINGA_OUTPUT := STRINGA_OUTPUT || LISTA_OUTPUT(I) || SELF.SEPARATORE;
END LOOP;
STRINGA_OUTPUT := STRINGA_OUTPUT || LISTA_OUTPUT(LISTA_OUTPUT.LAST);
-- se la stringa supera la dimensione massima impostata, tronca e termina con un terminatore
IF LENGTH(STRINGA_OUTPUT) > LUNGHEZZA_MAX THEN
RETURN_VALUE := SUBSTR(STRINGA_OUTPUT, 0, LUNGHEZZA_MAX - LENGTH(TERMINATORE)) || TERMINATORE;
ELSE
RETURN_VALUE:=STRINGA_OUTPUT;
END IF;
ELSE -- se non esiste nessun elemento, restituisci NULL
RETURN_VALUE := NULL;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LISTAGG_DISTINCT, CTX2 IN T_LISTAGG_DISTINCT) RETURN NUMBER IS
BEGIN
RETURN ODCICONST.SUCCESS;
END;
END; -- fine corpo
CREATE
FUNCTION LISTAGG_DISTINCT (INPUT LISTAGG_DISTINCT_PARAMS) RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING T_LISTAGG_DISTINCT;
// Example
SELECT LISTAGG_DISTINCT(LISTAGG_DISTINCT_PARAMS(OWNER, '', '')) AS LISTA_OWNER
FROM SYS.ALL_OBJECTS;
Lo siento, pero en algún caso (para un conjunto muy grande), Oracle podría devolver este error:
Object or Collection value was too large. The size of the value
might have exceeded 30k in a SORT context, or the size might be
too big for available memory.
pero creo que este es un buen punto de partida;)
La forma más simple de manejar múltiples listasgg es usar 1 WITH (factor de subconsulta) por columna que contenga un listagg de esa columna de un select distinct:
WITH tab AS
(
SELECT 1 as col1, 2 as col2, 3 as col3, ''Smith'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 2 as col2, 3 as col3,''John'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 3 as col2, 4 as col3,''Ajay'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 4 as col2, 4 as col3,''Ram'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 5 as col2, 6 as col3,''Jack'' as created_by FROM dual
)
, getCol2 AS
(
SELECT DISTINCT col1, listagg(col2,'','') within group (order by col2) over (partition by col1) AS col2List
FROM ( SELECT DISTINCT col1,col2 FROM tab)
)
, getCol3 AS
(
SELECT DISTINCT col1, listagg(col3,'','') within group (order by col3) over (partition by col1) AS col3List
FROM ( SELECT DISTINCT col1,col3 FROM tab)
)
select col1,col2List,col3List
FROM getCol2
JOIN getCol3
using (col1)
Lo que da:
col1 col2List col3List
1 2,3,4,5 3,4,6
Para evitar el problema de longitud de cadena, puede usar XMLAGG que es similar a listagg pero devuelve un clob.
Luego puede analizar usando regexp_replace y obtener los valores únicos y luego convertirlo nuevamente en una cadena usando dbms_lob.substr() . Si tiene una gran cantidad de valores distintos, de todos modos se quedará sin espacio, pero en muchos casos el código siguiente debería funcionar.
También puede cambiar los delimitadores que usa. En mi caso, quería ''-'' en lugar de '','' pero debería poder reemplazar los guiones en mi código y usar comas si así lo desea.
select col1,
dbms_lob.substr(ltrim(REGEXP_REPLACE(REPLACE(
REPLACE(
XMLAGG(
XMLELEMENT("A",col2)
ORDER BY col2).getClobVal(),
''<A>'',''-''),
''</A>'',''''),''([^-]*)(-/1)+($|-)'',
''/1/3''),''-''), 4000,1) as platform_mix
from table
Puedes hacerlo a través del reemplazo de RegEx. Aquí hay un ejemplo:
-- Citations Per Year - Cited Publications main query. Includes list of unique associated core project numbers, ordered by core project number.
SELECT ptc.pmid AS pmid, ptc.pmc_id, ptc.pub_title AS pubtitle, ptc.author_list AS authorlist,
ptc.pub_date AS pubdate,
REGEXP_REPLACE( LISTAGG ( ppcc.admin_phs_org_code ||
TO_CHAR(ppcc.serial_num,''FM000000''), '','') WITHIN GROUP (ORDER BY ppcc.admin_phs_org_code ||
TO_CHAR(ppcc.serial_num,''FM000000'')),
''(^|,)(.+)(,/2)+'', ''/1/2'')
AS projectNum
FROM publication_total_citations ptc
JOIN proj_paper_citation_counts ppcc
ON ptc.pmid = ppcc.pmid
AND ppcc.citation_year = 2013
JOIN user_appls ua
ON ppcc.admin_phs_org_code = ua.admin_phs_org_code
AND ppcc.serial_num = ua.serial_num
AND ua.login_id = ''EVANSF''
GROUP BY ptc.pmid, ptc.pmc_id, ptc.pub_title, ptc.author_list, ptc.pub_date
ORDER BY pmid;
También publicado aquí: Oracle - valores únicos de Listagg
Si desea valores distintos en columnas MÚLTIPLES, desea control sobre el orden de clasificación, no desea utilizar una función no documentada que puede desaparecer y no desea más de una exploración de tabla completa, puede encontrar útil esta herramienta:
with test_data as
(
select ''A'' as col1, ''T_a1'' as col2, ''123'' as col3 from dual
union select ''A'', ''T_a1'', ''456'' from dual
union select ''A'', ''T_a1'', ''789'' from dual
union select ''A'', ''T_a2'', ''123'' from dual
union select ''A'', ''T_a2'', ''456'' from dual
union select ''A'', ''T_a2'', ''111'' from dual
union select ''A'', ''T_a3'', ''999'' from dual
union select ''B'', ''T_a1'', ''123'' from dual
union select ''B'', ''T_b1'', ''740'' from dual
union select ''B'', ''T_b1'', ''846'' from dual
)
select col1
, (select listagg(column_value, '','') within group (order by column_value desc) from table(collect_col2)) as col2s
, (select listagg(column_value, '','') within group (order by column_value desc) from table(collect_col3)) as col3s
from
(
select col1
, collect(distinct col2) as collect_col2
, collect(distinct col3) as collect_col3
from test_data
group by col1
);
Si la intención es aplicar esta transformación a múltiples columnas, he extendido una solución de a_horse_with_no_name:
SELECT * FROM
(SELECT LISTAGG(GRADE_LEVEL, '','') within group(order by GRADE_LEVEL) "Grade Levels" FROM (select distinct GRADE_LEVEL FROM Students) t) t1,
(SELECT LISTAGG(ENROLL_STATUS, '','') within group(order by ENROLL_STATUS) "Enrollment Status" FROM (select distinct ENROLL_STATUS FROM Students) t) t2,
(SELECT LISTAGG(GENDER, '','') within group(order by GENDER) "Legal Gender Code" FROM (select distinct GENDER FROM Students) t) t3,
(SELECT LISTAGG(CITY, '','') within group(order by CITY) "City" FROM (select distinct CITY FROM Students) t) t4,
(SELECT LISTAGG(ENTRYCODE, '','') within group(order by ENTRYCODE) "Entry Code" FROM (select distinct ENTRYCODE FROM Students) t) t5,
(SELECT LISTAGG(EXITCODE, '','') within group(order by EXITCODE) "Exit Code" FROM (select distinct EXITCODE FROM Students) t) t6,
(SELECT LISTAGG(LUNCHSTATUS, '','') within group(order by LUNCHSTATUS) "Lunch Status" FROM (select distinct LUNCHSTATUS FROM Students) t) t7,
(SELECT LISTAGG(ETHNICITY, '','') within group(order by ETHNICITY) "Race Code" FROM (select distinct ETHNICITY FROM Students) t) t8,
(SELECT LISTAGG(CLASSOF, '','') within group(order by CLASSOF) "Expected Graduation Year" FROM (select distinct CLASSOF FROM Students) t) t9,
(SELECT LISTAGG(TRACK, '','') within group(order by TRACK) "Track Code" FROM (select distinct TRACK FROM Students) t) t10,
(SELECT LISTAGG(GRADREQSETID, '','') within group(order by GRADREQSETID) "Graduation ID" FROM (select distinct GRADREQSETID FROM Students) t) t11,
(SELECT LISTAGG(ENROLLMENT_SCHOOLID, '','') within group(order by ENROLLMENT_SCHOOLID) "School Key" FROM (select distinct ENROLLMENT_SCHOOLID FROM Students) t) t12,
(SELECT LISTAGG(FEDETHNICITY, '','') within group(order by FEDETHNICITY) "Federal Race Code" FROM (select distinct FEDETHNICITY FROM Students) t) t13,
(SELECT LISTAGG(SUMMERSCHOOLID, '','') within group(order by SUMMERSCHOOLID) "Summer School Key" FROM (select distinct SUMMERSCHOOLID FROM Students) t) t14,
(SELECT LISTAGG(FEDRACEDECLINE, '','') within group(order by FEDRACEDECLINE) "Student Decl to Prov Race Code" FROM (select distinct FEDRACEDECLINE FROM Students) t) t15
Esta es la versión 11.2.0.2.0 de Oracle Database 11g Enterprise Edition - Producción de 64 bits.
No pude usar STRAGG porque no hay forma de DISTINCT y ORDER.
El rendimiento escala linealmente, lo cual es bueno, ya que estoy agregando todas las columnas de interés. Lo anterior tomó 3 segundos para 77K filas. Por solo un resumen, .172 segundos. Sí, había una manera de distinguir múltiples columnas en una tabla en una sola pasada.
Si no necesita un orden particular de valores concatenados, y el separador puede ser una coma, puede hacer:
select col1, stragg(distinct col2)
from table
group by col1
Superé este problema agrupando los valores primero, luego hago otra agregación con el listagg. Algo como esto:
select a,b,listagg(c,'','') within group(order by c) c, avg(d)
from (select a,b,c,avg(d)
from table
group by (a,b,c))
group by (a,b)
solo un acceso a la tabla completa, relativamente fácil de expandir a consultas más complejas
Te refieres a algo como esto:
select listagg(the_column, '','') within group (order by the_column)
from (
select distinct the_column
from the_table
) t
Si necesita más columnas, algo como esto podría ser lo que está buscando:
select col1, listagg(col2, '','') within group (order by col2)
from (
select col1,
col2,
row_number() over (partition by col1, col2 order by col1) as rn
from foo
order by col1,col2
)
where rn = 1
group by col1;
Tuve una versión DISTINTA de esto y conseguí que funcionara.
RTRIM(REGEXP_REPLACE(
(value, '', '') WITHIN GROUP( ORDER BY value)),
''([^ ]+)(, /1)+'',''/1''),'', '')
Un aspecto molesto con LISTAGG es que si la longitud total de la cadena concatenada excede los 4000 caracteres (límite para VARCHAR2 en SQL), se LISTAGG el siguiente error, que es difícil de administrar en las versiones de Oracle hasta 12.1
ORA-01489: el resultado de la concatenación de cadenas es demasiado largo
Una nueva característica agregada en 12cR2 es la cláusula ON OVERFLOW LISTAGG de LISTAGG . La consulta que incluye esta cláusula se vería así:
SELECT pid, LISTAGG(Desc, '' '' on overflow truncate) WITHIN GROUP (ORDER BY seq) AS desc
FROM B GROUP BY pid;
Lo anterior restringirá la salida a 4000 caracteres pero no arrojará el error ORA-01489 .
Estas son algunas de las opciones adicionales de la cláusula ON OVERFLOW :
-
ON OVERFLOW TRUNCATE ''Contd..'': Esto mostrará''Contd..''al final de la cadena (El valor predeterminado es...) -
ON OVERFLOW TRUNCATE '''': Esto mostrará los 4000 caracteres sin ninguna cadena de terminación. -
ON OVERFLOW TRUNCATE WITH COUNT: Esto mostrará el número total de caracteres al final después de los caracteres de terminación. Ej .: - ''...(5512)'' -
ON OVERFLOW ERROR: si espera que elLISTAGGfalle con el errorORA-01489(que de todos modos es el predeterminado).
Use la función listagg_clob creada así:
create or replace package list_const_p
is
list_sep varchar2(10) := '','';
end list_const_p;
/
sho err
create type listagg_clob_t as object(
v_liststring varchar2(32767),
v_clob clob,
v_templob number,
static function ODCIAggregateInitialize(
sctx IN OUT listagg_clob_t
) return number,
member function ODCIAggregateIterate(
self IN OUT listagg_clob_t, value IN varchar2
) return number,
member function ODCIAggregateTerminate(
self IN OUT listagg_clob_t, returnValue OUT clob, flags IN number
) return number,
member function ODCIAggregateMerge(
self IN OUT listagg_clob_t, ctx2 IN OUT listagg_clob_t
) return number
);
/
sho err
create or replace type body listagg_clob_t is
static function ODCIAggregateInitialize(sctx IN OUT listagg_clob_t)
return number is
begin
sctx := listagg_clob_t('''', '''', 0);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(
self IN OUT listagg_clob_t,
value IN varchar2
) return number is
begin
if nvl(lengthb(v_liststring),0) + nvl(lengthb(value),0) <= 4000 then
self.v_liststring:=self.v_liststring || value || list_const_p.list_sep;
else
if self.v_templob = 0 then
dbms_lob.createtemporary(self.v_clob, true, dbms_lob.call);
self.v_templob := 1;
end if;
dbms_lob.writeappend(self.v_clob, length(self.v_liststring), v_liststring);
self.v_liststring := value || list_const_p.list_sep;
end if;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(
self IN OUT listagg_clob_t,
returnValue OUT clob,
flags IN number
) return number is
begin
if self.v_templob != 0 then
dbms_lob.writeappend(self.v_clob, length(self.v_liststring), self.v_liststring);
dbms_lob.trim(self.v_clob, dbms_lob.getlength(self.v_clob) - 1);
else
self.v_clob := substr(self.v_liststring, 1, length(self.v_liststring) - 1);
end if;
returnValue := self.v_clob;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT listagg_clob_t, ctx2 IN OUT listagg_clob_t) return number is
begin
if ctx2.v_templob != 0 then
if self.v_templob != 0 then
dbms_lob.append(self.v_clob, ctx2.v_clob);
dbms_lob.freetemporary(ctx2.v_clob);
ctx2.v_templob := 0;
else
self.v_clob := ctx2.v_clob;
self.v_templob := 1;
ctx2.v_clob := '''';
ctx2.v_templob := 0;
end if;
end if;
if nvl(lengthb(self.v_liststring),0) + nvl(lengthb(ctx2.v_liststring),0) <= 4000 then
self.v_liststring := self.v_liststring || ctx2.v_liststring;
ctx2.v_liststring := '''';
else
if self.v_templob = 0 then
dbms_lob.createtemporary(self.v_clob, true, dbms_lob.call);
self.v_templob := 1;
end if;
dbms_lob.writeappend(self.v_clob, length(self.v_liststring), self.v_liststring);
dbms_lob.writeappend(self.v_clob, length(ctx2.v_liststring), ctx2.v_liststring);
self.v_liststring := '''';
ctx2.v_liststring := '''';
end if;
return ODCIConst.Success;
end;
end;
/
sho err
CREATE or replace FUNCTION listagg_clob (input varchar2) RETURN clob
PARALLEL_ENABLE AGGREGATE USING listagg_clob_t;
/
sho err
listagg () ignora los valores NULL, por lo que en un primer paso podría usar la función lag () para analizar si el registro anterior tenía el mismo valor, si es así, entonces NULL, sino ''nuevo valor''.
WITH tab AS
(
SELECT 1 as col1, 2 as col2, ''Smith'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 2 as col2, ''John'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 3 as col2, ''Ajay'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 4 as col2, ''Ram'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 5 as col2, ''Jack'' as created_by FROM dual
)
SELECT col1
, CASE
WHEN lag(col2) OVER (ORDER BY col2) = col2 THEN
NULL
ELSE
col2
END as col2_with_nulls
, created_by
FROM tab;
Resultados
COL1 COL2_WITH_NULLS CREAT
---------- --------------- -----
1 2 Smith
1 John
1 3 Ajay
1 4 Ram
1 5 Jack
Tenga en cuenta que el segundo 2 se reemplaza por NULL. Ahora puede envolver un SELECT con el listagg () a su alrededor.
WITH tab AS
(
SELECT 1 as col1, 2 as col2, ''Smith'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 2 as col2, ''John'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 3 as col2, ''Ajay'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 4 as col2, ''Ram'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 5 as col2, ''Jack'' as created_by FROM dual
)
SELECT listagg(col2_with_nulls, '','') WITHIN GROUP (ORDER BY col2_with_nulls) col2_list
FROM ( SELECT col1
, CASE WHEN lag(col2) OVER (ORDER BY col2) = col2 THEN NULL ELSE col2 END as col2_with_nulls
, created_by
FROM tab );
Resultado
COL2_LIST
---------
2,3,4,5
Puedes hacer esto en varias columnas también.
WITH tab AS
(
SELECT 1 as col1, 2 as col2, ''Smith'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 2 as col2, ''John'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 3 as col2, ''Ajay'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 4 as col2, ''Ram'' as created_by FROM dual
UNION ALL SELECT 1 as col1, 5 as col2, ''Jack'' as created_by FROM dual
)
SELECT listagg(col1_with_nulls, '','') WITHIN GROUP (ORDER BY col1_with_nulls) col1_list
, listagg(col2_with_nulls, '','') WITHIN GROUP (ORDER BY col2_with_nulls) col2_list
, listagg(created_by, '','') WITHIN GROUP (ORDER BY created_by) created_by_list
FROM ( SELECT CASE WHEN lag(col1) OVER (ORDER BY col1) = col1 THEN NULL ELSE col1 END as col1_with_nulls
, CASE WHEN lag(col2) OVER (ORDER BY col2) = col2 THEN NULL ELSE col2 END as col2_with_nulls
, created_by
FROM tab );
Resultado
COL1_LIST COL2_LIST CREATED_BY_LIST
--------- --------- -------------------------
1 2,3,4,5 Ajay,Jack,John,Ram,Smith
puede usar la función wm_concat no wm_concat .
select col1, wm_concat(distinct col2) col2_list
from tab1
group by col1;
esta función devuelve la columna de clob, si lo desea, puede usar dbms_lob.substr para convertir clob a varchar2.