SQL Query per concatenare i valori delle colonne da più righe in Oracle

Sarebbe ansible build SQL per concatenare i valori delle colonne da più righe?

Quanto segue è un esempio:

Tabella A

 PID
 UN
 B
 C

Tabella B

 PID SEQ Desc

 A 1 Have
 A 2 un bello
 Un 3 giorni
 B 1 Bel lavoro.
 C 1 Sì
 C 2 possiamo 
 C 3 fare 
 C 4 questo lavoro!

L’output di SQL dovrebbe essere –

     Descrizione PID
     Buona giornata
     B Bel lavoro.
     C Sì, possiamo fare questo lavoro!
    

    Quindi in pratica la colonna Desc della tabella out put è una concatenazione dei valori SEQ della Tabella B?

    Qualche aiuto con l’SQL?

    Ci sono alcuni modi in base alla versione che si possiede: consultare la documentazione di Oracle sulle tecniche di aggregazione delle stringhe . Uno molto comune è usare LISTAGG :

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

    Quindi unisciti a A per scegliere i pids che desideri.

    Nota: Out of the box, LISTAGG funziona correttamente solo con le colonne VARCHAR2 .

    C’è anche una funzione XMLAGG , che funziona su versioni precedenti alla 11.2. Poiché WM_CONCAT non è documentato e non è supportato da Oracle , si consiglia di non utilizzarlo nel sistema di produzione.

    Con XMLAGG puoi fare quanto segue:

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

    Quello che fa è questo

    • metti i valori della colonna ename (concatenati con una virgola) dalla tabella employee_names in un elemento xml (con tag E)
    • estrai il testo di questo
    • aggregare l’xml (concatenarlo)
    • chiama la colonna risultante “Risultato”

    Con clausola del modello 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. 

    Ho scritto su questo qui . E se segui il link al thread OTN ne troverai di più, incluso un confronto delle prestazioni.

    La funzione analitica LISTAGG è stata introdotta in Oracle 11g Release 2 , rendendo molto semplice l’aggregazione delle stringhe. Se stai usando 11g Release 2 dovresti usare questa funzione per l’aggregazione di stringhe. Si prega di fare riferimento al di sotto dell’URL per ulteriori informazioni sulla concatenazione delle stringhe.

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

    Concatenazione di stringhe

    Come la maggior parte delle risposte suggeriscono, LISTAGG è l’opzione ovvia. Tuttavia, un aspetto fastidioso di LISTAGG è che se la lunghezza totale della stringa concatenata supera i 4000 caratteri (limite per VARCHAR2 in SQL), viene generato l’errore seguente, che è difficile da gestire nelle versioni Oracle fino a 12.1

    ORA-01489: il risultato della concatenazione di stringhe è troppo lungo

    Una nuova funzionalità aggiunta in 12cR2 è la clausola ON OVERFLOW di LISTAGG . La query che include questa clausola sarà simile a:

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

    Quanto sopra limiterà l’output a 4000 caratteri ma non ORA-01489 errore ORA-01489 .

    Queste sono alcune delle opzioni aggiuntive della clausola ON OVERFLOW :

    • ON OVERFLOW TRUNCATE 'Contd..' : Questo mostrerà 'Contd..' alla fine della stringa (l’impostazione predefinita è ... )
    • ON OVERFLOW TRUNCATE '' : Questo visualizzerà i 4000 caratteri senza alcuna stringa di terminazione.
    • ON OVERFLOW TRUNCATE WITH COUNT : questo mostrerà il numero totale di caratteri alla fine dopo la terminazione dei caratteri. Ad esempio: – ‘ ...(5512)
    • ON OVERFLOW ERROR : Se si prevede che LISTAGG non riesca con l’errore ORA-01489 (che è comunque predefinito).

    Per coloro che devono risolvere questo problema utilizzando Oracle 9i (o versioni precedenti), sarà probabilmente necessario utilizzare SYS_CONNECT_BY_PATH, poiché LISTAGG non è disponibile.

    Per rispondere all’OP, la seguente query visualizzerà il PID dalla Tabella A e concatenerà tutte le colonne DESC dalla Tabella 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; 

    Potrebbero inoltre esserci casi in cui chiavi e valori sono tutti contenuti in una tabella. La seguente query può essere utilizzata dove non esiste la tabella A e esiste solo la tabella 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; 

    Tutti i valori possono essere riordinati come desiderato. Le singole descrizioni concatenate possono essere riordinate nella clausola PARTITION BY e l’elenco dei PID può essere riordinato nella clausola finale ORDER BY.


    In alternativa: ci possono essere momentjs in cui vuoi concatenare tutti i valori di un’intera tabella in una riga.

    L’idea chiave qui sta usando un valore artificiale per il gruppo di descrizioni da concatenare.

    Nella seguente query, viene utilizzata la stringa costante “1”, ma qualsiasi valore funzionerà:

     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; 

    Le singole descrizioni concatenate possono essere riordinate nella clausola PARTITION BY.

    Molte altre risposte in questa pagina hanno menzionato anche questo riferimento estremamente utile: https://oracle-base.com/articles/misc/string-aggregation-techniques

    Prima di eseguire una query di selezione, esegui questo:

    SET SERVEROUT ON SIZE 6000

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

    Io uso il LISTAGG ma restituisco questa stringa per la stringa persiana!

    la mia domanda:

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

    risultato:

     'A7'1 , ,4F 

    Mi aiuti per favore.

    wow questa soluzione è funzionante:

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

    11g o superiore: Usa listagg :

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

    10g o inferiore: un metodo consiste nell’utilizzare una funzione:

     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; / 

    Per utilizzare la funzione:

     select col1, get_comma_separated_value(col1) from table_name 

    Nota: esiste una funzione (non supportata) WM_CONCAT disponibile su alcune versioni precedenti di Oracle, che potrebbero essere d’aiuto: vedere qui per i dettagli .

    In MySQL:

     SELECT col1, GROUP_CONCAT(col2) FROM table_name GROUP BY col1 
    1. LISTAGG offre le migliori prestazioni se l’ordinamento è obbligatorio (00: 00: 05,85)

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

    2. COLLECT offre le migliori prestazioni se l’ordinamento non è necessario (00: 00: 02.90):

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

    3. RACCOGLI con l’ordine è un po ‘più 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;

    Tutte le altre tecniche erano più lente.

    Prova questo codice:

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

    O la funzione Oracle STRAGG (colonna).

    Devo dire che questo tipo di elaborazione è molto limitato … se superi la larghezza del campo o la larghezza del display …

    Nella selezione in cui si desidera la concatenazione, chiamare una funzione SQL.

    Per esempio:

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

    Quindi per la funzione 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 syntax della funzione Header potrebbe essere errata, ma il principio funziona.