Comment avoir dans son result set un groupement de string qui serait agrégé et séparé par des virgules? Nous allons prendre comme cas d'utilisation la table des pays du schéma HR fournit par Oracle (HR.COUNTRIES). Nous voulons avoir pour chaque lettre de l'alphabet la liste des pays séparé par des virgules correspondant à cette lettre.
Il existe diverses solutions en Oracle pour arriver à cette fin. La plus récente (Oracle 11.2g) consiste à utiliser la fonction LISTAGG et de réaliser un group by. La requete suivante donne le résultat attendu :
La clause WITHIN GROUP(...) permet de réaliser un tri au sein de l'agrégation et ainsi avoir les pays triés par ligne du result set.
Pour les malchanceux(comme moi) qui n'ont pas la chance d'utiliser Oracle 11.2g, quelles sont les solutions à notre disposition pour réaliser la tâche demandée ? Après quelques recherches sur le Web, j'ai pu trouver la fonction wm_concat du package wmsys. La requete utilisant cette fonction est la suivante :
Cependant, cette solution n'est pas géniale. Premièrement, le result set n'est pas trié par ligne et la clause WITHIN GROUP n'est pas applicable sur cette fonction. La solution serait alors d'utiliser un PARTITION BY dans le select et d'ensuite filtrer pour n'avoir que les résultats qui contiennent l'ensemble des pays :
Le second problème lié à cette solution vient de sa non-documentation. En effet, cette fonction est undocumented et vous ne pourrez donc pas bénéficier du support d'Oracle en cas de problème avec cette fonction. De plus, la version exacte de la solution est assez lourde.
Quelles sont les solutions si on utilise une version d'Oracle un peu moins récente et qu'on ne veut pas utiliser une fonction non documentée ? Il existe sans doute plusieurs solutions à ce problème, les unes les plus originales et farfelues que les autres. Celle que j'ai dernièrement utilisée est la suivante :
Cette solution utilise tout d'abord la fonction d'agrégation RANK pour mettre un ranking sur les noms de pays portant la même première lettre. Ensuite, nous utilisons les clauses connect by et la fonction sys_connect_by_path pour agréger les pays qui porte la même première lettre et de réaliser le tri a au sein de chaque résultat.
Enfin, il est bien évidemment possible de réaliser sa propre fonction qui permet de réaliser cette agrégation de strings. Plusieurs sites présentent leurs implémentations(voir sources)
Articles
Aucun commentaire:
Enregistrer un commentaire