Requêtes pivots sous MS-SQL Server

Avancé

Les requêtes pivots ont été largement popularisées par les QBE de Microsoft, que ce soit via MS-Query, MS-Excel ou MS-Access.

Mais qu'en est-il de leur traitement via SQL en général, et MS-SQL en particulier ?

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

Les requêtes pivots ont été largement popularisées par les QBE de Microsoft, que ce soit via MS-Query, MS-Excel ou MS-Access. Elles ont de plus pris une importance considérable dans les outils d'aide à la décision, leur présentation agrégée permettant de condenser une information en une forme lisible, permettant l'analyse et la comparaison aisée de chiffres. Mais qu'en est-il de leur traitement via SQL en général, et MS-SQL en particuliers ?

Soit la table suivante

Produit Année Vente
A 2005 12'000
B 2006 15'000
C 2005 1'000
A 2006 12'500
C 2004 850
B 2004 18'000
A 2004 10'000
C 2003 1'100
A 2003 9'000

Code pour la reproduction

 
Sélectionnez

Create table ventes
(produit char(1),
annee int,
vente float
)

insert into ventes values('A', 2005, 12000)
insert into ventes values('B', 2006, 15000)
insert into ventes values('C', 2005, 1000)
insert into ventes values('A', 2006, 12500)
insert into ventes values('C', 2004, 850)
insert into ventes values('B', 2004, 18000)
insert into ventes values('A', 2004, 10000)
insert into ventes values('C', 2003, 1100)
insert into ventes values('A', 2003, 9000)

La représentation recherchée est la suivante:

  2003 2004 2005 2006
A 9'000 10'000 12'000 12'500
B 0 18'000 0 15'000
C 1'100 850 1'000 0

II. Le point de vue SQL

Le SQL (Structured Query Language) est, comme son nom l'indique, un language structuré permettant de traiter et de rendre l'information. Il sert donc de langue de communication entre l'application cliente et la base de données. Il a donc pour vocation de retourner les données stockées sur un SGBDR de amnière brute, l'application cliente étant ensuite responsable de retourner l'information formatée comme attendue par l'utilsiateur final.

Afin de simplifier la vie de leurs développeurs, la plupart des éditeurs de SGBDR ont très rapidement créés leur propre langage de programmation, permettant d'aller ainsi plus loin dans les traitements de l'information.

  • Microsoft SQL Server= Transact-SQL
  • Oracle = PL-SQL
  • Sybase = Transact-SQL

Ces extension du SQL permettent de faire des traitements sur les enregistrements, et même un peu de cosmétique (formatage, traitement de chaîne, conversion, ...). Ce faisant, on dénature un peu l'architecture multi-tiers: C'est en fait l'applicatif client qui devrait se charger de la cosmétique et de la mise en valeur de l'information.

III. Le traitement via les outils Microsoft

III-A. MS-Excel

Microsoft Excel, au travers de son assistant, a sans doute été l'un des pionniers de la popularisation des tables pivots de par son moteur d'analyse croisée dynamique. Rien de plus simple à utiliser.

Cliquez ici pour afficher la démonstration sous Excel.

III-B. MS-Access

Microsoft Access permet lui aussi de créer des tables pivots grace à ses requêtes d'analyse croisée.

Cliquez ici pour afficher la démonstration sous Access.

... ce qui donne ce SQL... bien peu normé et quelque peu propriétaire:

 
Sélectionnez
TRANSFORM Sum(Vente) AS [Somme de vente]
SELECT Produit
FROM Ventes
GROUP BY Produit
PIVOT Annee;

III-C. SQL Server

III-B-1. Versions pré-2005 : diverses méthodes

Il y a la méthode "bourrin" qui utilise une table temporaire et des mises à jours successives. Extrêmement coûteuse (chaque année supplémentaire génère un update supplémentaire), elle a le mérite d'être appréhendable par la majeure partie des débutants. C'est le parfait exemple d'un traitement séquentiel avec un langage typé ensembliste...

 
Sélectionnez

select distinct produit, 0 [2003], 0 [2004], 0 [2005], 0 [2006]
into #result
from Ventes

update #result 
set [2003] = Vente
from Ventes v, #result r
where v.annee=2003 
and v.produit=r.produit

update #result 
set [2004] = Vente
from Ventes v, #result r
where v.annee=2004
and v.produit=r.produit

update #result 
set [2005] = Vente
from Ventes v, #result r
where v.annee=2005
and v.produit=r.produit

update #result 
set [2006] = Vente
from Ventes v, #result r
where v.annee=2006
and v.produit=r.produit

select * from #result
drop table #result

Ensuite vient la méthode que nous pourrions baptiser "propriétaire", qui utilise fortement les sous-requêtes.

 
Sélectionnez

SELECT produit, 
       (SELECT SUM(vente)
        FROM   Ventes
        WHERE  annee = 2003
          AND  produit = VTE.produit) AS [2003],
       (SELECT SUM(vente)
        FROM   Ventes
        WHERE  annee = 2004
          AND  produit = VTE.produit) AS [2004],
       (SELECT SUM(vente)
        FROM   Ventes
        WHERE  annee = 2005
          AND  produit = VTE.produit) AS [2005],
       (SELECT SUM(vente)
        FROM   Ventes
        WHERE  annee = 2006
          AND  produit = VTE.produit) AS [2006]
FROM   Ventes VTE
GROUP  BY produit
Plan d'exécution méthode propriétaire

Nettement plus élégante, ma préférence va à celle-ci...

 
Sélectionnez

select 
		Produit    , 
        sum(case when annee=2003 then vente else  0 end) as [2003], 
        sum(case when annee=2004 then vente else  0 end) as [2004], 
        sum(case when annee=2005 then vente else  0 end) as [2005], 
        sum(case when annee=2006 then vente else  0 end) as [2006], 
        sum(vente ) as somme 
from Ventes  
group by produit 
Plan d'exécution méthode élégante

Il y a ensuite moyen de trafiquer une de ces méthodes pour la rendre quelque peu plus dynamique.

 
Sélectionnez

declare c cursor  for select distinct annee  from ventes order by annee
declare @sql varchar(2000), @col int
set @sql=''
open c
fetch c into @col
while @@FETCH_STATUS = 0
	begin
	select @sql=@sql+'SUM( case when annee='+ cast(@col as varchar(30)) +' then vente else 0 end) as ['+ cast(@col as varchar(30)) +'],'
	fetch c into @col
	end
close c
deallocate  c

exec ('SELECT Produit, '+ left(@sql,len(@sql)-1) +'  from Ventes group by Produit' )

III-C-2. Versions pré-2000: transposition matricielle

Plus compliquée, mais élégante et plus performante, la méthode de la transposition de matrice. Elle a l'avantage d'être portable sur quasi tous les SGBDR car elle n'utilise pas de SQL spécifique (tels que le CASE, les sous-requêtes, ...)

Elle utilise la spécificité qu'à le calcul matriciel de "retourner les données" lors de la multiplication par une matrice de 0 dont la diagonale est à 1.

J'en étais assez fier avant de trouver que cette méthode avait en fait déjà été exposée dans divers articles, dont l'excellent livre "SQL Avancé de Joe Celko", issue dune idée de John M Baird... bien avant 2000 !

 
Sélectionnez

CREATE TABLE matrice(
	[Produit] char(1) PRIMARY KEY,
	[2003] int NOT NULL CONSTRAINT [DF_matrice2_2003]  DEFAULT 0,
	[2004] int NOT NULL CONSTRAINT [DF_matrice2_2004]  DEFAULT 0,
	[2005] int NOT NULL CONSTRAINT [DF_matrice2_2005]  DEFAULT 0,
	[2006] int NOT NULL CONSTRAINT [DF_matrice2_2006]  DEFAULT 0)

insert into matrice (Produit)
select distinct Produit 
from Ventes

select matrice.Produit as Produit , 
  sum ((1-abs (sign(2003-v.annee)))*vente) as [2003], 
  sum ((1-abs (sign(2004-v.annee)))*vente) as [2004], 
  sum ((1-abs (sign(2005-v.annee)))*vente) as [2005], 
  sum ((1-abs (sign(2006-v.annee)))*vente) as [2006]
from Ventes v, matrice where v.produit=matrice.produit 
group by matrice.produit 
go 

III-C-3. Version 2005 : PIVOT

Bien que les méthodes vues ci-dessus marchent toujours, le T-SQL de Microsoft SQL Server 2005 accepte maintenant une notion de pivot:

 
Sélectionnez

SELECT Produit, 
	   SUM([2003]) AS [2003], 
	   SUM([2004]) AS [2004], 
	   SUM([2005]) AS [2005], 
	   SUM([2006]) AS [2006] 
FROM  Ventes
PIVOT (SUM(Vente) FOR annee IN ([2003], [2004], [2005], [2006])) AS Annees
GROUP BY Produit
Plan d'exécution méthode Pivot

... ce qui donne, en reprenant notre automatisation d'antan

 
Sélectionnez

declare c cursor  for select distinct annee  from ventes order by annee
declare @pivot varchar(200), @sum varchar(200), @sql nvarchar(1000), @col int

select @pivot='',  @sum=''

open c
fetch c into @col
while @@FETCH_STATUS = 0
	begin
	select @sum = @sum + ' SUM(['+ cast(@col as varchar(30)) +']) AS ['+ cast(@col as varchar(30)) +'],',
		   @pivot = @pivot + ' ['+ cast(@col as varchar(30)) +'], '
	fetch c into @col
	end
close c
deallocate  c

set @sql = 'SELECT Produit, '+ left(@sum, len(@sum)-1)+ 
			' FROM Ventes PIVOT (SUM(Vente) 
			FOR annee IN ('+ left(@pivot, len(@pivot)-1)+ ')) AS Annees 
			GROUP BY Produit'

exec(@sql)

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2006 fadace. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.