Requête SQL pour concaténer les valeurs de colonne de plusieurs lignes dans Oracle

Il existe plusieurs manières selon la version que vous avez – consultez la documentation d’Oracle sur les techniques d’agrégation de chaînes . Un très commun est d’utiliser LISTAGG :

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

Joignez-vous ensuite à A pour choisir les pids vous voulez.

Remarque: LISTAGG ne fonctionne correctement qu’avec les colonnes VARCHAR2 .

Il y a aussi une fonction XMLAGG , qui fonctionne sur les versions antérieures à 11.2. WM_CONCAT étant non documenté et non pris en charge par Oracle , il est recommandé de ne pas l’utiliser dans le système de production.

Avec XMLAGG vous pouvez effectuer les opérations suivantes:

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

Qu’est-ce que cela fait est

• mettre les valeurs de la colonne ename (concaténées avec une virgule) de la table employee_names dans un élément xml (avec la balise E)
• extraire le texte de cette
• agréger le xml (concaténer)
• appeler la colonne résultante “Résultat”

Avec la clause de modèle SQL:

 SQL> select pid 2 , lsortingm(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 à ce sujet ici . Et si vous suivez le lien vers le fil OTN, vous en trouverez d’autres, y compris une comparaison des performances.

La fonction analytique LISTAGG a été introduite dans Oracle 11g Release 2 , ce qui la rend très facile à agréger. Si vous utilisez 11g Release 2, vous devez utiliser cette fonction pour l’agrégation de chaînes. S’il vous plaît se référer ci-dessous url pour plus d’informations sur la concaténation de chaînes.

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

Concaténation de chaînes

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

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

Une nouvelle fonctionnalité ajoutée à 12cR2 est la clause ON OVERFLOW LISTAGG de LISTAGG . La requête incluant cette clause ressemblerait à ceci:

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

Ce qui précède restreindra la sortie à 4000 caractères mais ne lancera pas l’erreur ORA-01489 .

Voici quelques options supplémentaires de la clause ON OVERFLOW :

• ON OVERFLOW TRUNCATE 'Contd..' : Ceci affichera 'Contd..' à la fin de la chaîne (la valeur par défaut est ... )
• ON OVERFLOW TRUNCATE '' : Ceci affichera les 4000 caractères sans chaîne de fin.
• ON OVERFLOW TRUNCATE WITH COUNT : Ceci affichera le nombre total de caractères à la fin après les caractères de fin. Ex: – ‘ ...(5512) ‘
• ON OVERFLOW ERROR : Si vous prévoyez que LISTAGG échoue avec l’erreur ORA-01489 (qui est la valeur par défaut de toute façon).

Pour ceux qui doivent résoudre ce problème avec Oracle 9i (ou antérieur), vous devrez probablement utiliser SYS_CONNECT_BY_PATH, car LISTAGG n’est pas disponible.

Pour répondre à l’OP, la requête suivante affichera le PID du tableau A et concaténera toutes les colonnes DESC du tableau 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 également y avoir des cas où les clés et les valeurs sont toutes contenues dans une table. La requête suivante peut être utilisée lorsqu’il n’y a pas de table A et que seule la table 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 réorganisées à volonté. Les descriptions concaténées individuelles peuvent être réorganisées dans la clause PARTITION BY et la liste des PID peut être réorganisée dans la clause ORDER BY finale.

Sinon, il peut arriver que vous souhaitiez concaténer toutes les valeurs d’une table entière en une seule ligne.

L’idée clé ici est d’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 toute 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 clause PARTITION BY.

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

Avant d’exécuter une requête de sélection, 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 retourne 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, s’il vous plaît.

wow cette solution est travaillée:

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

11g et plus: Utilisez listagg :

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

10g et moins: 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 

Remarque: Il existe une fonction (non prise en charge) WM_CONCAT disponible sur certaines anciennes versions d’Oracle, ce qui peut vous aider – voir ici .

Dans MySQL:

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

1. LISTAGG offre les meilleures performances si le sorting est indispensable (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 sorting 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. COLLECT avec la commande est un peu moins rapide (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 que ce genre de traitement est très limité … si vous dépassez la largeur du champ ou la largeur d’affichage …

Dans la sélection où vous voulez votre concaténation, appelez une fonction SQL.

Par exemple:

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

Ensuite, 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 incorrecte, mais le principe fonctionne.