Requête SQL pour concaténer les valeurs des colonnes à partir de plusieurs lignes dans Oracle

il y a aussi une fonction XMLAGG , qui fonctionne sur les versions antérieures à 11.2. Parce que WM_CONCAT est non documentée et non supportée par Oracle , il est recommandé de ne pas l'utiliser dans le système de production.

avec XMLAGG vous pouvez faire ce qui suit:

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

ce Que ce n'est

• mettez les valeurs de la colonne ename (concaténée avec une virgule) de la employee_names table dans un élément xml (avec la balise E)
• extrait du texte de ce
• agréger les données xml (concaténer)
• appelez la colonne résultante "résultat "

avec clause modèle 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.

j'ai écrit à propos de ce ici . Et si vous suivez le lien vers le thread OTN, vous en trouverez d'autres, y compris une comparaison des performances.

la LISTAGG fonction analytique a été introduite dans Oracle 11g version 2 , ce qui rend très facile à agréger les chaînes. Si vous utilisez la version 2 de 11g, vous devez utiliser cette fonction pour l'agrégation des chaînes. Consultez l'url ci-dessous pour plus d'informations sur la concaténation de chaîne.

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

Concaténation De Chaîne

comme la plupart des réponses le suggèrent, LISTAGG est l'option évidente. Cependant ,un aspect gênant avec LISTAGG est que si la longueur totale de chaîne concaténée dépasse 4000 caractères( limite pour VARCHAR2 en SQL ), l'erreur ci-dessous est lancée, ce qui est difficile à gérer dans les versions Oracle jusqu'à 12.1

ORA-01489: résultat de la concaténation de la chaîne est trop long

une nouvelle caractéristique ajoutée dans 12cR2 est la clause ON OVERFLOW de LISTAGG . La requête incluant cette clause ressemblerait à:

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

ce qui précède limitera la sortie à 4000 caractères mais ne lancera pas l'erreur ORA-01489 .

Ces sont quelques-uns des autres options de ON OVERFLOW clause:

• ON OVERFLOW TRUNCATE 'Contd..' : cela affichera 'Contd..' à la fin de la chaîne (par défaut est ... )
• ON OVERFLOW TRUNCATE '' : cela affichera les 4000 caractères sans aucune terminaison de chaîne.
• ON OVERFLOW TRUNCATE WITH COUNT : cela affichera le total nombre de caractères à la fin, après les caractères de fin. Par exemple:- ' ...(5512) '
• ON OVERFLOW ERROR : si vous vous attendez à ce que le LISTAGG échoue avec le ORA-01489 erreur (qui est par défaut de toute façon ).

pour ceux qui doivent résoudre ce problème en utilisant Oracle 9i (ou une version antérieure), vous devrez probablement utiliser SYS_CONNECT_BY_PATH, puisque LISTAGG n'est pas disponible.

pour répondre à L'OP, la requête suivante affichera le PID de la Table A et concaténera toutes les colonnes des DESC de la Table 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;

il peut aussi y avoir des cas où les clés et les valeurs sont toutes contenues dans un tableau. La requête suivante peut être utilisée lorsqu'il n'y a pas de Table A, et seul le tableau 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;

toutes les valeurs peuvent être ordonnancées comme désiré. Les descriptions concaténées individuelles peuvent être réorganisées dans la PARTITION par clause, et la liste des PID peut être réorganisée dans l'ordre final par clause.

alternativement: il peut y avoir des moments où vous voulez concaténer toutes les valeurs d'une table entière dans une rangée.

l'idée clé ici est utiliser une valeur artificielle pour le groupe de descriptions à concaténer.

dans la requête suivante, la chaîne constante '1' est utilisée, mais n'importe quelle valeur fonctionnera:

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;

les descriptions concaténées individuelles peuvent être réorganisées dans la PARTITION par clause.

plusieurs autres réponses sur cette page ont également mentionné cette référence extrêmement utile: https://oracle-base.com/articles/misc/string-aggregation-techniques

avant d'exécuter une requête select, exécutez ceci:

SET SERVEROUT ON SIZE 6000

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

j'utilise le LISTAGG mais rends cette chaîne pour la chaîne persane !

ma requête:

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

résultat:

'A7'1 , ,4F

Aidez-moi.

wow cette solution est travaillée:

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

11g et plus: Use listagg :

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

10g et inférieur: une méthode consiste à utiliser une fonction:

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

pour utiliser la fonction:

select col1, get_comma_separated_value(col1) from table_name

Note: il existe une fonction (non supportée) WM_CONCAT disponible sur certaines versions anciennes D'Oracle, qui pourrait vous aider à sortir - voir ici pour plus de détails .

In MySQL:

SELECT col1, GROUP_CONCAT(col2) FROM table_name GROUP BY col1

1. LISTAGG offre les meilleures performances si le tri est un must(00:00:05.85)

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

2. COLLECT offre les meilleures performances si le tri n'est pas nécessaire(00:00:02.90):

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

3. collecter à la commande est un peu plus lent(00:00:07.08):

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

toutes les autres techniques étaient plus lentes.

essayez ce code:

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

ou la fonction Oracle STRAGG(colonne).

je dois dire, ce genre de traitement est très limité ... si vous dépassez la largeur de champ ou la largeur d'affichage ...

dans le select où vous voulez votre concaténation, appelez une fonction SQL.

par exemple:

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

puis pour la fonction 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 syntaxe de L'en-tête de fonction peut être erronée, mais le principe fonctionne.