Comment puis-je combiner plusieurs lignes dans une liste délimitée par des virgules dans Oracle ?

Question

J'ai une question simple :

select * from countries

avec les résultats suivants :

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

Je voudrais renvoyer les résultats en une seule ligne, comme ceci :

Albania, Andorra, Antigua, ...

Bien sûr, je peux écrire une fonction PL/SQL pour faire ce travail (je l'ai déjà fait dans Oracle 10g), mais existe-t-il une solution plus agréable, de préférence non spécifique à Oracle (ou peut-être une fonction intégrée) pour cette tâche ?

Je l'utilise généralement pour éviter les rangs multiples dans une sous-requête. Ainsi, si une personne a plus d'une nationalité, je ne veux pas qu'elle figure en double dans la liste.

Ma question est basée sur la question similaire posée sur SQL server 2005 .

UPDATE : Ma fonction ressemble à ceci :

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

Le site WM_CONCAT (si elle est incluse dans votre base de données, avant Oracle 11.2) ou la fonction LISTAGG (à partir d'Oracle 11.2) devrait faire l'affaire. Par exemple, ceci permet d'obtenir une liste délimitée par des virgules des noms de tables dans votre schéma :

select listagg(table_name, ', ') within group (order by table_name) 
  from user_tables;

ou

select wm_concat(table_name) 
  from user_tables;

Plus de détails/options

Lien vers la documentation

Answer 2

Voici une méthode simple sans stragg ni création de fonction.

create table countries ( country_name varchar2 (100));

insert into countries values ('Albania');

insert into countries values ('Andorra');

insert into countries values ('Antigua');

SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
      FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
                   COUNT (*) OVER () cnt
              FROM countries)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

CSV                                                                             
--------------------------
Albania,Andorra,Antigua                                                         

1 row selected.

Comme d'autres l'ont mentionné, si vous êtes sur 11g R2 ou plus, vous pouvez maintenant utiliser listagg qui est beaucoup plus simple.

select listagg(country_name,', ') within group(order by country_name) csv
  from countries;

CSV                                                                             
--------------------------
Albania, Andorra, Antigua

1 row selected.

Answer 3

Pour Oracle, vous pouvez utiliser LISTAGG

Answer 4

Vous pouvez également l'utiliser :

SELECT RTRIM (
          XMLAGG (XMLELEMENT (e, country_name || ',')).EXTRACT ('//text()'),
          ',')
          country_name
  FROM countries;

Answer 5

Vous pouvez essayer cette requête.

select listagg(country_name,',') within group (order by country_name) cnt 
from countries;