unir - Consulta SQL para concatenar valores de columna de varias filas en Oracle

oracle transponer registros columnas

Question

¿Sería posible construir SQL para concatenar valores de columna de múltiples filas?

Lo siguiente es un ejemplo:

Tabla A

PID A B C

Tabla B

PID SEQ Desc A 1 Have A 2 a nice A 3 day. B 1 Nice Work. C 1 Yes C 2 we can C 3 do C 4 this work!

La salida del SQL debería ser -

PID Desc A Have a nice day. B Nice Work. C Yes we can do this work!

Entonces, básicamente, ¿la columna Desc para la tabla de salida es una concatenación de los valores de SEQ de la Tabla B?

Cualquier ayuda con el SQL?

Answer 1

1. LISTAGG ofrece el mejor rendimiento si la clasificación es obligatoria (00: 00: 05.85)

   SELECT pid, LISTAGG(Desc, '' '') WITHIN GROUP (ORDER BY seq) AS description FROM B GROUP BY pid;

2. COLLECT ofrece el mejor rendimiento si la clasificación no es necesaria (00: 00: 02.90):

   SELECT pid, TO_STRING(CAST(COLLECT(Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;

3. COLLECT con el pedido es un poco más lento (00: 00: 07.08):

   SELECT pid, TO_STRING(CAST(COLLECT(Desc ORDER BY Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;

Todas las demás técnicas fueron más lentas.

Answer 2

Antes de ejecutar una consulta de selección, ejecute esto:

SET SERVEROUT ON SIZE 6000

SELECT XMLAGG(XMLELEMENT(E,SUPLR_SUPLR_ID||'','')).EXTRACT(''//text()'') "SUPPLIER"
FROM SUPPLIERS;

Answer 3

Como la mayoría de las respuestas sugieren, LISTAGG es la opción obvia. Sin embargo, un aspecto molesto con LISTAGG es que si la longitud total de la cadena concatenada supera 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 el LISTAGG falle con el error ORA-01489 (que de todos modos es el predeterminado).

Answer 4

Con la cláusula modelo de SQL:

SQL> select pid
2 , ltrim(sentence) sentence
3 from ( select pid
4 , seq
5 , sentence
6 from b
7 model
8 partition by (pid)
9 dimension by (seq)
10 measures (descr,cast(null as varchar2(100)) as sentence)
11 ( sentence[any] order by seq desc
12 = descr[cv()] || '' '' || sentence[cv()+1]
13 )
14 )
15 where seq = 1
16 /
P SENTENCE
- ---------------------------------------------------------------------------
A Have a nice day
B Nice Work.
C Yes we can do this work!
3 rows selected.

Escribí sobre esto here . Y si sigue el enlace al hilo OTN, encontrará algunos más, incluida una comparación de rendimiento.

Answer 5

En la selección donde desea su concatenación, llame a una función SQL.

Por ejemplo:

select PID, dbo.MyConcat(PID)
from TableA;

Luego, para la función SQL:

Function MyConcat(@PID varchar(10))
returns varchar(1000)
as
begin
declare @x varchar(1000);
select @x = isnull(@x +'','', @x, @x +'','') + Desc
from TableB
where PID = @PID;
return @x;
end

La sintaxis del encabezado de función puede ser incorrecta, pero el principio funciona.

Answer 6

Estoy usando LISTAGG pero devuelvo esta cadena para cadena persa.

mi consulta:

SELECT
listagg(DESCRIPTION,'' , '') within group (order by DESCRIPTION)
FROM
B_CEREMONY

resultado:

''A7''1 , ,4F

Por favor, ayúdame.

wow esta solución funciona:

SELECT listagg(convert(DESCRIPTION, ''UTF8'', ''AL16UTF16''),'' , '') within group
(order by DESCRIPTION)
FROM B_CEREMONY;

Answer 7

La función analítica LISTAGG se introdujo en Oracle 11g Release 2 , por lo que es muy fácil agregar cadenas. Si está utilizando 11g Release 2, debe usar esta función para la agregación de cadenas. Consulte la URL siguiente para obtener más información sobre la concatenación de cadenas.

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

Concatenación de cadenas

Answer 8

O la función Oracle STRAGG (columna).

Tengo que decir que este tipo de procesamiento es muy limitado ... si excedes el ancho del campo o ancho de la pantalla ...

Answer 9

Para aquellos que deben resolver este problema usando Oracle 9i (o anterior), probablemente necesites usar SYS_CONNECT_BY_PATH, ya que LISTAGG no está disponible.

Para responder al OP, la siguiente consulta mostrará el PID de la Tabla A y concatenará todas las columnas DESC de la Tabla B:

SELECT pid, SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, '', '')), 3) all_descriptions
FROM (
SELECT ROW_NUMBER () OVER (PARTITION BY pid ORDER BY pid, seq) rnum, pid, description
FROM (
SELECT a.pid, seq, description
FROM table_a a, table_b b
WHERE a.pid = b.pid(+)
)
)
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR pid = pid
GROUP BY pid
ORDER BY pid;

También puede haber instancias donde las claves y los valores están todos contenidos en una tabla. La siguiente consulta se puede usar donde no hay Tabla A, y solo existe la Tabla B:

SELECT pid, SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, '', '')), 3) all_descriptions
FROM (
SELECT ROW_NUMBER () OVER (PARTITION BY pid ORDER BY pid, seq) rnum, pid, description
FROM (
SELECT pid, seq, description
FROM table_b
)
)
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR pid = pid
GROUP BY pid
ORDER BY pid;

Todos los valores se pueden reordenar como se desee. Las descripciones concatenadas individuales se pueden reordenar en la cláusula PARTITION BY, y la lista de PID se puede reordenar en la cláusula final ORDER BY.

Alternativamente: puede haber ocasiones en las que desee concatenar todos los valores de una tabla completa en una fila.

La idea clave aquí es usar un valor artificial para el grupo de descripciones que se concatenarán.

En la siguiente consulta, se utiliza la cadena constante ''1'', pero cualquier valor funcionará:

SELECT SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, '', '')), 3) all_descriptions
FROM (
SELECT ROW_NUMBER () OVER (PARTITION BY unique_id ORDER BY pid, seq) rnum, description
FROM (
SELECT ''1'' unique_id, b.pid, b.seq, b.description
FROM table_b b
)
)
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1;

Las descripciones concatenadas individuales se pueden reordenar en la cláusula PARTITION BY.

Varias otras respuestas en esta página también han mencionado esta referencia extremadamente útil: https://oracle-base.com/articles/misc/string-aggregation-techniques

Answer 10

Prueba este código:

 SELECT XMLAGG(XMLELEMENT(E,fieldname||'','')).EXTRACT(''//text()'') "FieldNames"
FROM FIELD_MASTER
WHERE FIELD_ID > 10 AND FIELD_AREA != ''NEBRASKA'';

Answer 11

También hay una función XMLAGG , que funciona en versiones anteriores a la 11.2. Debido a que WM_CONCAT no está documentado y no es compatible con Oracle , se recomienda no utilizarlo en el sistema de producción.

Con XMLAGG puede hacer lo siguiente:

SELECT XMLAGG(XMLELEMENT(E,ename||'','')).EXTRACT(''//text()'') "Result"
FROM employee_names

Lo que esto hace es

• poner los valores de la columna ename (concatenada con una coma) de la tabla employee_names en un elemento xml (con la etiqueta E)
• extraer el texto de esto
• agregar el xml (concatenarlo)
• llamar a la columna resultante "Resultado"

Answer 12

11g y superior: use listagg :

SELECT
col1,
LISTAGG(col2, '', '') WITHIN GROUP (ORDER BY col2) "names"
FROM table_x
GROUP BY col1

10g o menos: un método es usar una función:

CREATE OR REPLACE FUNCTION get_comma_separated_value (input_val in number)
RETURN VARCHAR2
IS
return_text VARCHAR2(10000) := NULL;
BEGIN
FOR x IN (SELECT col2 FROM table_name WHERE col1 = input_val) LOOP
return_text := return_text || '','' || x.col2 ;
END LOOP;
RETURN LTRIM(return_text, '','');
END;
/

Para usar la función:

select col1, get_comma_separated_value(col1) from table_name

Nota: Existe una función (no WM_CONCAT ) WM_CONCAT disponible en ciertas versiones anteriores de Oracle, que podría WM_CONCAT útil: consulte aquí para obtener más información .

En MySQL:

SELECT col1, GROUP_CONCAT(col2) FROM table_name GROUP BY col1

Answer 13

Hay algunas formas dependiendo de la versión que tenga: consulte la documentación de Oracle sobre técnicas de agregación de cadenas . Una muy común es usar LISTAGG :

SELECT pid, LISTAGG(Desc, '' '') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;

Luego únete a A para elegir los pids que quieras.

Nota: Fuera de la caja, LISTAGG solo funciona correctamente con columnas VARCHAR2 .