Consulta SQL para concatenar valores de colunas de várias linhas no Oracle

Seria possível construir SQL para concatenar valores de coluna de várias linhas?

O seguinte é um exemplo:

Tabela A

 PID
 UMA
 B
 C

Tabela B

 PID SEQ Desc.

 A 1 Tem
 A 2 um bom
 3 dias
 B 1 bom trabalho.
 C 1 Sim
 C 2 nós podemos 
 C 3 faz 
 C 4 este trabalho!

Saída do SQL deve ser –

 Desc. PID
 Um bom dia.
 B bom trabalho.
 C Sim, podemos fazer este trabalho!

Então, basicamente, a coluna Desc para a tabela put é uma concatenação dos valores SEQ da Tabela B?

Qualquer ajuda com o SQL?

Existem algumas maneiras, dependendo da versão que você tem – veja a documentação da Oracle sobre técnicas de agregação de strings . Um muito comum é usar o LISTAGG :

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

Em seguida, junte-se a A para escolher os pids você deseja.

Nota: Fora da checkbox, o LISTAGG só funciona corretamente com colunas VARCHAR2 .

Há também uma function XMLAGG , que funciona em versões anteriores a 11.2. Como o WM_CONCAT está documentado e não é suportado pelo Oracle , é recomendado não usá-lo no sistema de produção.

Com XMLAGG você pode fazer o seguinte:

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

O que isso faz é

  • coloque os valores da coluna ename (concatenada com vírgula) da tabela employee_names em um elemento xml (com tag E)
  • extrair o texto desta
  • agregar o xml (concatenar)
  • chame a coluna resultante “Resultado”

Com cláusula de modelo 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. 

Eu escrevi sobre isso aqui . E se você seguir o link para o segmento OTN, você encontrará mais alguns, incluindo uma comparação de desempenho.

A function analítica LISTAGG foi introduzida no Oracle 11g Release 2 , facilitando muito a agregação de strings. Se você estiver usando o 11g Release 2, você deve usar esta function para agregação de strings. Por favor, consulte o URL abaixo para obter mais informações sobre a concatenação de strings.

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

Concatenação de Cordas

Como a maioria das respostas sugere, o LISTAGG é a opção óbvia. No entanto, um aspecto irritante com LISTAGG é que, se o comprimento total da cadeia concatenada exceder 4000 caracteres (limite para VARCHAR2 no SQL), o erro abaixo será lançado, o que é difícil de gerenciar nas versões do Oracle até 12.1

ORA-01489: resultado da concatenação de string é muito longo

Um novo recurso adicionado em 12cR2 é a cláusula ON OVERFLOW do LISTAGG . A consulta incluindo esta cláusula seria semelhante a:

 SELECT pid, LISTAGG(Desc, ' ' on overflow truncate) WITHIN GROUP (ORDER BY seq) AS desc FROM B GROUP BY pid; 

O acima irá restringir a saída para 4000 caracteres, mas não lançará o erro ORA-01489 .

Estas são algumas das opções adicionais da cláusula ON OVERFLOW :

  • ON OVERFLOW TRUNCATE 'Contd..' : Isto irá mostrar 'Contd..' no final da string (o padrão é ... )
  • ON OVERFLOW TRUNCATE '' : Isso exibirá os 4000 caracteres sem qualquer string de terminação.
  • ON OVERFLOW TRUNCATE WITH COUNT : Isso exibirá o número total de caracteres no final após os caracteres finais. Por exemplo: – ‘ ...(5512)
  • ON OVERFLOW ERROR : Se você espera que o LISTAGG falhe com o erro ORA-01489 (que é padrão de qualquer maneira).

Para aqueles que precisam resolver esse problema usando o Oracle 9i (ou anterior), você provavelmente precisará usar o SYS_CONNECT_BY_PATH, pois o LISTAGG não está disponível.

Para responder ao OP, a consulta a seguir exibirá o PID da Tabela A e concatenará todas as colunas DESC da Tabela 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; 

Também pode haver instâncias em que chaves e valores estejam todos contidos em uma tabela. A consulta a seguir pode ser usada onde não há Tabela A e somente a Tabela B existe:

 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 os valores podem ser reordenados conforme desejado. Descrições concatenadas individuais podem ser reordenadas na cláusula PARTITION BY e a lista de PIDs pode ser reordenada na cláusula ORDER BY final.


Como alternativa: pode haver momentos em que você deseja concatenar todos os valores de uma tabela inteira em uma linha.

A idéia principal aqui é usar um valor artificial para o grupo de descrições a serem concatenadas.

Na consulta a seguir, a string constante ‘1’ é usada, mas qualquer 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; 

Descrições individuais concatenadas podem ser reordenadas na cláusula PARTITION BY.

Várias outras respostas nesta página também mencionaram esta referência extremamente útil: https://oracle-base.com/articles/misc/string-aggregation-techniques

Antes de executar uma consulta de seleção, execute isto:

SET SERVEROUT ON SIZE 6000

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

Eu usando o LISTAGG mas retorno esta string para string persa!

minha consulta:

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

resultado:

 'A7'1 , ,4F 

Por favor me ajude.

nossa solução é trabalhada:

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

11g e superior: Use listagg :

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

10g e inferior: Um método é usar uma function:

 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 a function:

 select col1, get_comma_separated_value(col1) from table_name 

Nota: Existe uma function (não suportada) WM_CONCAT disponível em algumas versões anteriores do Oracle, o que pode ajudá-lo – veja aqui para detalhes .

No MySQL:

 SELECT col1, GROUP_CONCAT(col2) FROM table_name GROUP BY col1 
  1. LISTAGG oferece o melhor desempenho se a sorting for uma obrigação (00: 00: 05.85)

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

  2. O COLLECT oferece o melhor desempenho se a sorting não for necessária (00: 00: 02.90):

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

  3. COLECIONAR com o pedido é um pouco mais 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 as outras técnicas foram mais lentas.

Experimente este código:

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

Ou a function Oracle STRAGG (coluna).

Eu tenho que dizer, esse tipo de processamento é muito limitado … se você exceder a largura do campo ou a largura da canvas …

No select onde você quer sua concatenação, chame uma function SQL.

Por exemplo:

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

Então, para a function 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 

A syntax do header de function pode estar errada, mas o princípio funciona.