SQL Pivot Table

Le pivot est une technique que vous permet de faire pivoter la sortie d’une requête de ligne à colonne et inversement.

Comme par exemple transformer ce résultat :

Par ce résultat :

Commençons par la création des tables

Imaginons que nous avons un ancien fichier des tarifs contenant 4 tarifs (dans 4 colonnes pour la table TARIF_H et 4 enregistrements pour la table TARIF_V)

TARIF_H : Table avec des tarifs sur une seule ligne mais dans plusieurs colonnes

CREATE TABLE TARIF_H ( 
	FOURNISSEUR INTEGER DEFAULT 0, 
	ARTICLE VARCHAR(10) CCSID 1208 DEFAULT '' , 
	TARIF1 DECIMAL(14, 6) DEFAULT 0 , 
	TARIF2 DECIMAL(14, 6) DEFAULT 0 , 
	TARIF3 DECIMAL(14, 6) DEFAULT 0 , 
	TARIF4 DECIMAL(14, 6) DEFAULT 0 );

insert into TARIF_H values(1, 'ART1', 14.1, 12.2, 11.3, 10.9);
insert into TARIF_H values(1, 'ART2', 15.6, 15.0, 14.5, 14.0);

TARIF_V : Table avec des tarifs dans une colonne mais sur plusieurs lignes 

CREATE TABLE TARIF_V ( 
	FOURNISSEUR INTEGER DEFAULT 0 , 
	ARTICLE VARCHAR(10) CCSID 1208 DEFAULT '' , 
	NUMTARIF DECIMAL(2, 0) DEFAULT 0 , 
	TARIF DECIMAL(14, 6) DEFAULT 0 );

insert into TARIF_V values(1, 'ART1', 1, 14.1);
insert into TARIF_V values(1, 'ART1', 2, 12.2);
insert into TARIF_V values(1, 'ART1', 3, 11.3);
insert into TARIF_V values(1, 'ART1', 4, 10.9);
insert into TARIF_V values(1, 'ART2', 1, 15.6);
insert into TARIF_V values(1, 'ART2', 2, 15.0);
insert into TARIF_V values(1, 'ART2', 3, 14.5);
insert into TARIF_V values(1, 'ART2', 4, 14.0);

Passage d’un affichage horizontal à vertical

Utilisation du mot clé LATERAL pour faire une jointure LATERAL et afficher les tarifs sur plusieurs lignes. Nous allons la combiner avec la fonction VALUE ce qui nous permettra d’ajouter une colonne avec la position du tarif (Tarif colonne 1 = position tarif 1, Tarif colonne 2 = position tarif 2 etc…). 

select T.fournisseur, T.article, V.POSITIONTARIF, V.VALEURTARIF
  from FG.TARIF_H as T,
    LATERAL(VALUES (1, T.TARIF1),
                   (2, T.TARIF2),
                   (3, T.TARIF3),
                   (4, T.TARIF4)) as V(POSITIONTARIF, VALEURTARIF)
  where FOURNISSEUR = 1;

ps : La clause where n’est pas indispensable pour notre cas.

Résultat :

Imaginons que nous avons pour chaque tarif une colonne « date début tarif  » (et certainement fin de tarif 🙂 ).

alter table TARIF_H add column DATEDEBUTTARIF1 date;
alter table TARIF_H add column DATEDEBUTTARIF2 date;
alter table TARIF_H add column DATEDEBUTTARIF3 date;
alter table TARIF_H add column DATEDEBUTTARIF4 date;

Il faudra simplement l’ajouter dans la jointure LATERAL ainsi que dans la sélection des zones à afficher :

select T.fournisseur, T.article, V.POSITIONTARIF, V.VALEURTARIF, V.DATEDEBUTTARIF
  from FG.TARIF_H as T,
    LATERAL(VALUES (1, T.TARIF1, T.DATEDEBUTTARIF1),
                   (2, T.TARIF2, T.DATEDEBUTTARIF2),
                   (3, T.TARIF3, T.DATEDEBUTTARIF3),
                   (4, T.TARIF4, T.DATEDEBUTTARIF4)) as V(POSITIONTARIF, VALEURTARIF, DATEDEBUTTARIF)
  where FOURNISSEUR = 1;

Résultat :

Passage d’un affichage vertical à horizontal

Cette fois-ci nous allons utiliser la fonction d’agrégation MAX (même si nous savons qu’il n’y aura qu’un tarif avec le numéro 1, 2, 3 etc…) avec un groupage sur le fournisseur et numéro d’article.

select fournisseur, article,
    max(case when numtarif = 1 then tarif end) as Tarif1,  
    max(case when numtarif = 2 then tarif end) as Tarif2,  
    max(case when numtarif = 3 then tarif end) as Tarif3,
    max(case when numtarif = 4 then tarif end) as Tarif4 
from TARIF_V
group by fournisseur, article;

Résultat :

Nous sommes d’accord qu’il existe d’autres méthodes pour faire pivoter des données (listagg etc…).

/par