Requête SQL pour concaténer les valeurs de colonnes de plusieurs lignes en Oracle

Question

Est-il possible de construire une requête SQL pour concaténer les valeurs de colonnes de plusieurs lignes?

Voici un exemple :

Tableau A

PID
A
B
C

Tableau B

PID   SEQ    Desc

A     1      Avoir
A     2      une belle
A     3      journée.
B     1      Beau travail.
C     1      Oui
C     2      nous pouvons 
C     3      faire 
C     4      ce travail!

La sortie de la requête SQL devrait être -

PID   Desc
A     Avoir une belle journée.
B     Beau travail.
C     Oui nous pouvons faire ce travail!

En gros, la colonne Desc du tableau de sortie est une concaténation des valeurs SEQ du tableau B?

Une aide avec la requête SQL?

Answer 1

Il existe quelques façons selon la version que vous avez - consultez la documentation oracle sur les techniques d'agrégation de chaînes. Une méthode très courante est d'utiliser LISTAGG:

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

Ensuite, rejoignez A pour sélectionner les pids souhaités.

Note: Par défaut, LISTAGG fonctionne correctement uniquement avec les colonnes VARCHAR2.

Answer 2

Il existe également une fonction XMLAGG, qui fonctionne sur les versions antérieures à la version 11.2. Étant donné que WM_CONCAT est non documenté et non pris en charge par Oracle, il est recommandé de ne pas l'utiliser dans un système de production.

Avec XMLAGG, vous pouvez faire ce qui suit:

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

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 cela
• agréger le xml (le concaténer)
• appeler la colonne résultante "Résultat"

Answer 3

Avec la 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 à ce sujet ici. Et si vous suivez le lien vers le fil OTN, vous trouverez quelques autres, y compris une comparaison des performances.

Answer 4

La fonction analytique LISTAGG a été introduite dans Oracle 11g Release 2, ce qui rend très facile l'agrégation de chaînes de caractères. Si vous utilisez 11g Release 2, vous devriez utiliser cette fonction pour l'agrégation de chaînes de caractères. Veuillez consulter l'URL ci-dessous pour plus d'informations sur la concaténation de chaînes de caractères.

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

Concaténation de chaînes de caractères

Answer 5

Comme la plupart des réponses le suggèrent, LISTAGG est l'option évidente. Cependant, un aspect ennuyeux avec 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 déclenchée, ce qui est difficile à gérer dans les versions d'Oracle jusqu'à 12.1

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

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

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

Cela limitera la sortie à 4000 caractères mais ne déclenchera pas l'erreur ORA-01489.

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

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