Oracle Database/Utilisation de fonctions/Fonction UNPIVOT

La méthode UNPIVOT permet de transformer des colonnes en lignes supplémentaires. Son principe est d'introduire 2 nouvelles colonnes liées, à la place des N colonnes spécifiées :

  • la première colonne (N) dans le résultat indique le nom de la colonne
  • la deuxième colonne (V) indique la valeur de la colonne indiquée

Cette méthode peut être utile pour réduire le nombre de colonnes, et/ou appliquer aux valeurs stockées en colonnes les traitements applicables aux lignes.

Syntaxe :

UNPIVOT (colonne_valeur FOR colonne_nom IN (colonne_1, colonne_2, ...))
colonne_valeur
La nouvelle colonne (V) indiquant la valeur associée.
colonne_nom
La nouvelle colonne (N) indiquant le nom de la colonne transformée en ligne.
colonne_1, colonne_2, ...
Liste des colonnes à transformer en lignes.


Illustration

modifier

La requête suivante illustre la modification d'une ligne (à noter aussi l'utilisation d'une double concaténation).

-- UNPIVOT Exemple 1

with Mesure as (
   select 1 MesureID,4 Capteur1,3 Capteur2,5 Capteur3,4 Capteur4,4 Capteur5 from dual union    --Pour la 1ere ligne, on précise les noms des colonnes
   select 2 , 4 , 1 , 5 , 51, 5  from dual union                                                          --Pour les lignes, on ne le refait pas
   select 3 , 4 , 3 , 5 , 9 , 4  from dual union
   select 4 , 3 , 91, 5 , 5 , 4  from dual union
   select 5 , 4 , 1 , 5 , 5 , 5  from dual
   )
select * from Mesure

--***** Décommenter une ligne parmi les suivantes. Si aucune décommentée, le résultat sera l'affichage normal de la table temporaire "Mesure" *****
--UNPIVOT (ValeurColonne FOR Colonne IN (Capteur1))                                         -- Trivial. Une colonne "COLONNE" à valeur unique "CAPTEUR1" ajoutée : 5
--UNPIVOT (ValeurColonne FOR Colonne IN (Capteur1, Capteur2))                               -- Doublement du nombre de lignes    : 10
--UNPIVOT (ValeurColonne FOR Colonne IN (Capteur1, Capteur2, Capteur3))                     -- Triplement du nombre de lignes    : 15
--UNPIVOT (ValeurColonne FOR Colonne IN (Capteur1, Capteur2, Capteur3, Capteur4))           -- Quadruplement du nombre de lignes : 20
--UNPIVOT (ValeurColonne FOR Colonne IN (Capteur1, Capteur2, Capteur3, Capteur4, Capteur5)) -- Quintuplement du nombre de lignes : 25
;
  • Résultat sans rien décommenter :
MESUREID CAPTEUR1 CAPTEUR2 CAPTEUR3 CAPTEUR4 CAPTEUR5
-------- -------- -------- -------- -------- --------
1 4 3 5 4 4
2 4 1 5 51 5
3 4 3 5 9 4
4 3 91 5 5 4
5 4 1 5 5 5
  • Résultat en décommenter la 1ère ligne UNPIVOT :
MESUREID CAPTEUR2 CAPTEUR3 CAPTEUR4 CAPTEUR5 COLONNE VALEURCOLONNE
-------- -------- -------- -------- -------- -------- -------------
1 3 5 4 4 CAPTEUR1 4
2 1 5 51 5 CAPTEUR1 4
3 3 5 9 4 CAPTEUR1 4
4 91 5 5 4 CAPTEUR1 3
5 1 5 5 5 CAPTEUR1 4
  • Résultat en décommenter la dernière ligne UNPIVOT :
MESUREID COLONNE VALEURCOLONNE
---------- -------- -------------
1 CAPTEUR1 4
1 CAPTEUR2 3
1 CAPTEUR3 5
1 CAPTEUR4 4
1 CAPTEUR5 4
2 CAPTEUR1 4
2 CAPTEUR2 1
2 CAPTEUR3 5
2 CAPTEUR4 51
2 CAPTEUR5 5
3 CAPTEUR1 4
3 CAPTEUR2 3
3 CAPTEUR3 5
3 CAPTEUR4 9
3 CAPTEUR5 4
4 CAPTEUR1 3
4 CAPTEUR2 91
4 CAPTEUR3 5
4 CAPTEUR4 5
4 CAPTEUR5 4
5 CAPTEUR1 4
5 CAPTEUR2 1
5 CAPTEUR3 5
5 CAPTEUR4 5
5 CAPTEUR5 5
25 rows selected

Cas pratique d'utilisation

modifier

Gardons de la table exemple précédente la 1ere et la dernière ligne seulement. Le but sera de recenser les colonnes pour lesquelles ces lignes ont des valeurs différentes. Pour ce faire, on va

  1. d'abord transformer les colonnes en lignes avec UNPIVOT
  2. Puis dénombrer les valeurs ainsi transformées.
  3. Filtrer les lignes créées, de couples (colonne, valeur), qui sont présentes une seule fois (ie les colonnes dont la valeur a changé)
  4. Et enfin distinguer les noms de colonnes du précédent ensemble

L'exemple sera plus parlant :

-- UNPIVOT Exemple utilisation

with Mesure as (
   select 1 MesureID,4 Capteur1,3 Capteur2,5 Capteur3,4 Capteur4,4 Capteur5 from dual union    -- 1ere ligne
   select 5 , 4 , 1 , 5 , 5 , 5  from dual                                                     -- dernière ligne
   )
   
, Denombrement as (
   select COLONNE, VALEURCOLONNE, count(*) nombre from Mesure                                  -- On ne conserve que les 2 colonnes du couple (colonne, valeur), plus un dénombrement
   UNPIVOT (ValeurColonne FOR Colonne IN (Capteur1, Capteur2, Capteur3, Capteur4, Capteur5))   -- On converti en lignes l'intégralité des colonnes, sauf la PK
   group by COLONNE, VALEURCOLONNE
)

--select * from Mesure                                                                         -- Décommenter cette ligne pour voir les 2 lignes comparées
--select * from Denombrement                                                                   -- Décommenter cette ligne pour voir le dénombrement des valeurs
--select * from Denombrement where nombre=1                                                    -- Décommenter cette ligne pour voir le filtrage du dénombrement, ie les colonnes variantes
select distinct COLONNE from Denombrement where nombre=1                                       -- Il suffit juste de recenser les colonnes variantes
;
  • Résultat en décommentant le premier select (« select * from Mesure ») :
MESUREID CAPTEUR1 CAPTEUR2 CAPTEUR3 CAPTEUR4 CAPTEUR5
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
1 4 3 5 4 4
5 4 1 5 5 5
  • Résultat en décommentant le deuxième select (« select * from Denombrement ») :
COLONNE VALEURCOLONNE NOMBRE
-------- ---------------------- ----------------------
CAPTEUR2 3 1
CAPTEUR5 4 1
CAPTEUR2 1 1
CAPTEUR1 4 2
CAPTEUR3 5 2
CAPTEUR5 5 1
CAPTEUR4 4 1
CAPTEUR4 5 1
  • Résultat en décommentant le dernier select (« select distinct COLONNE from Denombrement where nombre=1 ») :
COLONNE
--------
CAPTEUR5
CAPTEUR2
CAPTEUR4

Généralisation de la requête précédente

modifier

Bien sûr, pour un exemple aussi simple que celui pris, il serait plus simple de recenser par soi même les colonnes variantes. Mais si la table considérée faisait 300 colonnes, avec des noms complexes, alors un recensement à la main serait extrêmement fastidieux et probablement source d'erreurs !

La démarche idéale supposerait d'agir en deux temps :

  1. Tout d'abord, lister les colonnes de la table cible
  2. Copier coller le résultat de la requête précédente en lieu et place de "Capteur1, Capteur2, Capteur3, Capteur4, Capteur5"