Recherche sur le blog

lundi 14 décembre 2015

[SQL] Concaténer en une seule chaine par ligne

Il existe, en MySQL, une fonction qui s'appelle GROUP_CONCAT et qui permet de concaténer - d'agréger - plusieurs éléments récupérés depuis une seule table en vue d'obtenir une seule chaine de caractères, par ligne de résultat. Cela permet, par exemple, de récupérer les informations d'un livre, avec les auteurs séparés par des ",", et ce en une seule ligne de résultat. L'exemple repris dans la documentation de MySQL permet d'afficher le nom des étudiants avec leurs résultats aux différents tests.
SELECT student_name, GROUP_CONCAT(test_score)
FROM student
GROUP BY student_name;
Avec DB2 c'est plus délicat et selon la version de la base de données, il faudra utiliser une fonction différente. Par exemple, dans les versions 9.2 et supérieures (corrigez-moi si je fais erreur), il existe la fonction LISTAGG qui reçoit en paramètre le champ pour lequel on veut concaténer les valeurs, et le séparateur.

La même fonction - LISTAGG - existe dans le SGBD Oracle, à partir de la version 11g. En ce qui concerne les versions inférieures on peut se dépanner avec une procédure stockée.

Sur AS/400, il a fallu trouver une alternative. Il faut donc utiliser des fonctions XML pour arriver à un résultat similaire, et si on le souhaite, utiliser la fonction REPLACE directement dans la requête pour modifier la chaîne résultante. C'est un peu plus lourd pour la lisibilité globale de la requête.
SELECT pc.ean, pc.libelle,
 (SELECT
  XMLSERIALIZE(
   XMLAGG(
    XMLELEMENT(NAME x, trim(auteur_nom))
   ) AS VARCHAR(1024) CCSID 500
  )
  FROM auteurs 
  JOIN art_auteurs on pc.ean = art_auteurs.ean 
  AND art_auteurs.idaut = auteurs.idaut)
   AS liste_auteurs
FROM articles AS pc 
On utilise 3 fonctions au total :
  • XMLSERIALIZE : va retourner une expression XML sérialisée, dans le type donné. Le paramètre correspond à l'expression XML. Lors des différents essais l'erreur SQL0332 s'est produite. Il faut faire attention à l'encodage par rapport au client, d'où l'utilisation de la clause CCSID dans l'exemple. Plus d'informations dans la documentation IBM.
  • XMLAGG : retourne une séquence XML avec un objet pour chaque valeur non-nulle contenue dans un ensemble de valeurs XML Le paramètre, dans l'exemple, est donc l'ensemble d'éléments "nom d'auteur". En savoir plus.
  • XMLELEMENT : renvoie un champ sous forme d'élément XML. Dans l'exemple, le nom de l'auteur sera transformé en NOM. Le premier paramètre correspond au nom de l'attribut, le second au champ à transformer. En savoir plus
Il suffit ensuite, soit dans votre programme soit directement dans la requête, de modifier la chaine pour y mettre des "," ou tout autre caractère de votre choix. Bon développement !

2 commentaires:

Reka a dit…

Ne jamais concaténer ou interpoler des données dans une requête SQL.
https://www.owasp.org/index.php/PHP_Security_Cheat_Sheet#Never_concatenate_or_interpolate_data_in_SQL

Vincent a dit…

Ici je parle de concaténation d'une chaine récupérée dans une BDD. Ce n'est pas au niveau de la construction de la requête :-)