Transposition dans les requêtes SQL (Oracle)

De Julien CORON dans Technique

21 mai 2012

La transposition est le fait d’inverser les lignes en colonnes. Nous allons voir comment Oracle a implémenté une des fonctions analytiques pour faire cette opération.

Supposons que nous avons une requête qui nous donne les données suivantes :

gateau nomProduit ordre
Gateau au chocolat Farine 1
Gateau au chocolat Beurre 3
Gateau au chocolat Lait 2
Gateau au chocolat Chocolat 4
Choux à la crème Farine 1
Choux à la crème Œufs 2

 

Voici la requête qui a extrait les données :

1
2
3
4
Select gateau, nomProduit, ordre
  from tableGateaux, tableProduitsGateaux, tableProduits
  where tableGateaux.idgateau = tableProduitsGateaux.idgateau
  and tableProduitsGateaux.idproduit = tableProduits.idproduit;

 

Si on désire avoir la liste des produits pour faire les gâteaux, dans l’ordre, avec une ligne par gâteau, voici le résultat attendu :

Gateau Produits
Gateau au chocolat Farine, Lait , Beurre, Chocolat
Choux à la crème Farine, Œufs

 

Pour obtenir ce résultat, il faut dire à Oracle qu’on va traiter gâteau par gâteau, en unifiant les produits ayant le même gâteau, dans l’ordre :

1
2
3
4
5
6
7
8
9
10
11
12
Select gateau, substr(max(sys_connect_by_path(nomProduit,',')),2) "produits"
From
(
  Select gateau, nomProduit, ordre
  from tableGateaux, tableProduitsGateaux, tableProduits
  where tableGateaux.idgateau = tableProduitsGateaux.idgateau
  and tableProduitsGateaux.idproduit = tableProduits.idproduit
)
Connect By ordre-1 = Prior ordre and gateau = Prior gateau
Start With ordre = 1
group by gateau
order by gateau;

Détail des mots-clé de la requête :

  • sys_connect_by_path(colonne, séparateur) : Il faut connecter les données avec le séparateur
  • substr(max( … ), 2) : Astuces pour afficher joliment les données (le séparateur est inséré après la dernière donnée.  Avec cette astuce, le dernier séparateur est supprimé)
  • Connect by donnée = Prior donnée : Il faut que la donnée précédente et la donnée suivante (à séparer par le séparateur) aient une contrainte particulière. Dans notre exemple : l’ordre -1 du produit suivant = l’ordre du produit précédent ET le gâteau est le même.

 

Les autres fonctions analytiques d’Oracle : http://lalystar.developpez.com/fonctionsAnalytiques/


Commentaire

1 + six =

iMDEO recrute !

REJOIGNEZ-NOUS

A la recherche de nouveaux talents (développeurs web et mobile, chefs de projet,...)

Voir les annonces