Wie kann ich in Oracle mehrere Zeilen zu einer kommagetrennten Liste zusammenfassen?

Question

Ich habe eine einfache Frage:

select * from countries

mit den folgenden Ergebnissen:

country_name
------------
Albania
Andorra
Antigua
.....

Ich möchte die Ergebnisse in einer Zeile zurückgeben, also etwa so:

Albania, Andorra, Antigua, ...

Natürlich kann ich eine PL/SQL-Funktion schreiben, um die Aufgabe zu erledigen (das habe ich bereits in Oracle 10g getan), aber gibt es eine schönere, vorzugsweise nicht Oracle-spezifische Lösung (oder vielleicht eine integrierte Funktion) für diese Aufgabe?

Ich würde es im Allgemeinen verwenden, um mehrere Zeilen in einer Unterabfrage zu vermeiden, d. h. wenn eine Person mehr als eine Staatsangehörigkeit hat, möchte ich nicht, dass sie in der Liste doppelt vorkommt.

Meine Frage bezieht sich auf eine ähnliche Frage auf SQL-Server 2005 .

UPDATE : Meine Funktion sieht folgendermaßen aus:

CREATE OR REPLACE FUNCTION APPEND_FIELD (sqlstr in varchar2, sep in varchar2 ) return varchar2 is
ret varchar2(4000) := '';
TYPE cur_typ IS REF CURSOR;
rec cur_typ;
field varchar2(4000);
begin
     OPEN rec FOR sqlstr;
     LOOP
         FETCH rec INTO field;
         EXIT WHEN rec%NOTFOUND;
         ret := ret || field || sep;
     END LOOP;
     if length(ret) = 0 then
          RETURN '';
     else
          RETURN substr(ret,1,length(ret)-length(sep));
     end if;
end;

Answer 1

Am schnellsten geht es, wenn Sie die Oracle-Collect-Funktion verwenden.

Sie können auch dies tun:

select *
  2    from (
  3  select deptno,
  4         case when row_number() over (partition by deptno order by ename)=1
  5             then stragg(ename) over
  6                  (partition by deptno
  7                       order by ename
  8                         rows between unbounded preceding
  9                                  and unbounded following)
 10         end enames
 11    from emp
 12         )
 13   where enames is not null

Besuchen Sie die Website ask tom und suchen Sie nach 'stragg' oder 'string concatenation'. Jede Menge Beispiele. Es gibt auch eine nicht dokumentierte Orakelfunktion, die Ihre Bedürfnisse erfüllt.

Answer 2

Ich brauchte etwas Ähnliches und fand die folgende Lösung.

select RTRIM(XMLAGG(XMLELEMENT(e,country_name || ',')).EXTRACT('//text()'),',') country_name from

Answer 3

In diesem Beispiel erstellen wir eine Funktion, mit der eine durch Kommata getrennte Liste von verschiedenen AP-Rechnungshaltegründen auf Zeilenebene in ein Feld für die Abfrage auf Kopfebene gebracht wird:

 FUNCTION getHoldReasonsByInvoiceId (p_InvoiceId IN NUMBER) RETURN VARCHAR2

  IS

  v_HoldReasons   VARCHAR2 (1000);

  v_Count         NUMBER := 0;

  CURSOR v_HoldsCusror (p2_InvoiceId IN NUMBER)
   IS
     SELECT DISTINCT hold_reason
       FROM ap.AP_HOLDS_ALL APH
      WHERE status_flag NOT IN ('R') AND invoice_id = p2_InvoiceId;
BEGIN

  v_HoldReasons := ' ';

  FOR rHR IN v_HoldsCusror (p_InvoiceId)
  LOOP
     v_Count := v_COunt + 1;

     IF (v_Count = 1)
     THEN
        v_HoldReasons := rHR.hold_reason;
     ELSE
        v_HoldReasons := v_HoldReasons || ', ' || rHR.hold_reason;
     END IF;
  END LOOP;

  RETURN v_HoldReasons;
END;

Answer 4

Ich musste dafür immer PL/SQL schreiben, oder ich füge einfach ein ',' an das Feld an, kopiere es in einen Editor und entferne das CR aus der Liste, wodurch ich die einzelne Zeile erhalte.

Das heißt,

select country_name||', ' country from countries

In beide Richtungen ein bisschen langatmig.

Wenn Sie bei Ask Tom nachschauen, werden Sie eine Menge möglicher Lösungen sehen, aber sie alle greifen auf Typdeklarationen und/oder PL/SQL zurück

Fragen Sie Tom

Answer 5

SELECT REPLACE(REPLACE
((SELECT     TOP (100) PERCENT country_name + ', ' AS CountryName
FROM         country_name
ORDER BY country_name FOR XML PATH('')), 
'&<CountryName>', ''), '&<CountryName>', '') AS CountryNames