SQL-Abfrage zum Verketten von Spaltenwerten aus mehreren Zeilen in Oracle

Es gibt auch eine XMLAGG-Funktion, die mit Versionen vor 11.2 funktioniert. Da WM_CONCATundokumentiert ist und von Oracle nicht unterstützt wird , wird empfohlen, es nicht im Produktionssystem zu verwenden.

Mit XMLAGG können Sie Folgendes tun:

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

Was macht das?

• füge die Werte der ename-Spalte (verkettet mit einem Komma) aus der employee_names-Tabelle in ein xml-Element (mit Tag E) ein
• extrahiere den Text davon
• xml aggregieren (verketten)
• rufe die resultierende Spalte "Ergebnis" auf

Mit SQL-Modellklausel:

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.

Ich habe darüber hier geschrieben. Und wenn Sie dem Link zum OTN-Thread folgen, finden Sie weitere Informationen, einschließlich eines Leistungsvergleichs.

Die Analysefunktion LISTAGG wurde in Oracle 11g Release 2 eingeführt. Dadurch ist es sehr einfach, Strings zu aggregieren. Wenn Sie 11g Release 2 verwenden, sollten Sie dies verwenden Funktion für die String-Aggregation . Weitere Informationen zur String-Verkettung finden Sie unter URL.

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

String-Verkettung

Wie die meisten Antworten vermuten lassen, ist LISTAGG die naheliegende Option. Ein ärgerlicher Aspekt bei LISTAGG ist jedoch, dass, wenn die Gesamtlänge der verketteten Zeichenfolge 4000 Zeichen überschreitet (Begrenzung für VARCHAR2 in SQL), der folgende Fehler ausgegeben wird, der in Oracle-Versionen bis 12.1 nur schwer zu verwalten ist 

ORA-01489: Ergebnis der String-Verkettung ist zu lang

Eine neue Funktion, die in 12cR2 hinzugefügt wurde, ist die ON OVERFLOW-Klausel von LISTAGG. Die Abfrage, die diese Klausel enthält, würde folgendermaßen aussehen:

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

Das obige wird die Ausgabe auf 4000 Zeichen beschränken, aber den ORA-01489-Fehler nicht auslösen.

Dies sind einige der zusätzlichen Optionen der ON OVERFLOW-Klausel:

• ON OVERFLOW TRUNCATE 'Contd..': Dies zeigt 'Contd..' am Dem Ende der Zeichenfolge an (Standard ist ...).
• ON OVERFLOW TRUNCATE '': Dies zeigt die 4000 Zeichen Ohne abschließende Zeichenfolge an.
• ON OVERFLOW TRUNCATE WITH COUNT: Dies zeigt die Gesamtzahl der Zeichen __. Am Ende nach den abschließenden Zeichen . Beispiel: - '...(5512)'
• ON OVERFLOW ERROR: Wenn Sie davon ausgehen, dass die LISTAGG mit dem ORA-01489-Fehler fehlschlägt (was ohnehin Standard ist).

Für diejenigen, die dieses Problem mit Oracle 9i (oder früher) lösen müssen, müssen Sie wahrscheinlich SYS_CONNECT_BY_PATH verwenden, da LISTAGG nicht verfügbar ist.

Um das OP zu beantworten, zeigt die folgende Abfrage die PID aus Tabelle A an und verkettet alle DESC-Spalten aus Tabelle 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;

Es kann auch Fälle geben, in denen Schlüssel und Werte in einer Tabelle enthalten sind. Die folgende Abfrage kann verwendet werden, wenn keine Tabelle A vorhanden ist und nur Tabelle B vorhanden ist:

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;

Alle Werte können wie gewünscht neu angeordnet werden. Einzelne verkettete Beschreibungen können in der PARTITION BY-Klausel neu angeordnet werden, und die Liste der PIDs kann in der endgültigen ORDER BY-Klausel neu angeordnet werden.

Alternativ: Es kann vorkommen, dass Sie alle Werte einer ganzen Tabelle in einer Zeile verketten möchten.

Die Schlüsselidee hierbei ist die Verwendung eines künstlichen Wertes für die zu verkettende Beschreibungsgruppe.

In der folgenden Abfrage wird die konstante Zeichenfolge '1' verwendet, jedoch funktioniert jeder Wert:

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;

Einzelne verkettete Beschreibungen können in der PARTITION BY-Klausel neu angeordnet werden.

Einige andere Antworten auf dieser Seite haben auch diese äußerst hilfreiche Referenz erwähnt: https://Oracle-base.com/articles/misc/string-aggregation-techniques

1. LISTAGG liefert die beste Leistung, wenn Sortieren ein Muss ist (00: 00: 05.85)

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

2. COLLECT bietet die beste Leistung, wenn keine Sortierung erforderlich ist (00: 00: 02.90):

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

3. COLLECT mit Bestellung ist etwas langsamer (00: 00: 07.08):

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

Alle anderen Techniken waren langsamer.

Bevor Sie eine Auswahlabfrage ausführen, führen Sie Folgendes aus: 

SET SERVEROUT ON SIZE 6000 

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

Ich benutze das LISTAGG, gebe aber diese Zeichenfolge für eine persische Zeichenfolge zurück!

meine Abfrage:

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

Ergebnis:

'A7'1 , ,4F

Bitte hilf mir.

wow diese Lösung funktioniert:

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