Etablir un tableau de bord (table CTE et PIVOT)

Afin de mieux prévoir le succès rencontré par les différents films, il est souhaitable d'établir un tableau de bord qui recevra en ligne les différents catégories, en colonnes les différents mois de l'année et dans les cellules le nombre de locations pour la catégorie et le mois concerné. Lorsqu'une location débute sur un mois et se termine sur un autre, c'est la date d'emprunt qui fait référence.

Exemple de résultat souhaité:

Comment aboutir à ce résultat?

Il n'est pas possible d'aboutir simplement, c'est-à-dire à l'aide d'une seule requête, au résultat souhaité, aussi est il nécessaire de travailler en 2 étapes.

1. Etablir la liste des catégories en comptant pour chaque mois le nombre de locations.
2. Utiliser une table CTE pour manipuler les données issues de la requête écrite à l'étape 1 et faire pivoter les données par rapport au mois.

Etablir la liste des catégories avec le nombre de location par mois

USE video;
go
SELECT categorie=c.libelle, mois=datepart(month,creele),emprunts=count(*)
FROM Magasin.Details md
INNER JOIN Magasin.Exemplaires me ON me.numero=md.exemplaire
INNER JOIN Magasin.Fiches mf ON mf.numero=md.fiche
INNER JOIN Films f on f.numero=me.film
INNER JOIN Categories c on c.code=f.categorie
GROUP BY c.libelle, datepart(month,creele);			
			

Puis élargir la requête pour obtenir ce résultat pour toutes les catégories, y compris celles pour lesquelles aucune location n'est comptabilisée:

SELECT categorie=c.libelle, mois=datepart(month,creele),emprunts=count(*)
FROM Magasin.Details md
INNER JOIN Magasin.Exemplaires me ON me.numero=md.exemplaire
INNER JOIN Magasin.Fiches mf ON mf.numero=md.fiche
INNER JOIN Films f on f.numero=me.film
RIGHT OUTER JOIN Categories c on c.code=f.categorie
GROUP BY c.libelle, datepart(month,creele);			
			

Enfin faire pivoter les données autour de la catégorie en effectuant la somme des emprunts pour une catégorie et un mois donnée:

USE video;
go
with catemois as(
SELECT categorie=c.libelle, mois=datepart(month,creele),emprunts=count(*)
FROM Magasin.Details md
INNER JOIN Magasin.Exemplaires me ON me.numero=md.exemplaire
INNER JOIN Magasin.Fiches mf ON mf.numero=md.fiche
INNER JOIN Films f on f.numero=me.film
RIGHT OUTER JOIN Categories c on c.code=f.categorie
GROUP BY c.libelle, datepart(month,creele))
select categorie, 
	isnull([1],0) as "Janvier",
	isnull([2],0) as "Février",
	isnull([3],0) as "Mars",
	isnull([4],0) as "Avril",
	isnull([5],0) as "Mai",
	isnull([6],0) as "Juin",
	isnull([7],0) as "Juillet",
	isnull([8],0) as "Août",
	isnull([9],0) as "Septembre",
	isnull([10],0) as "Octobre",
	isnull([11],0) as "Novembre",
	isnull([12],0) as "Décembre"
from catemois  pivot ( sum (emprunts) for mois in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as pvt;