Cross Tab Queries en SQL Server 2005

Les cross tab queries sont souvent bien utiles pour présenter des synthèses de résultats par catégories par exemple (En statistique cela est très utile pour l'ANOVA ou les tests du khi carré sur des comptes variables binaires).

Certains informaticiens ont le réflexe de récupérer les données via un data set puis de les trier eux même sous formes de tableau, cette opération est souvent très couteuse alors que SQL Server 2005 nous offre la commande PIVOT.

Cette commande permet de créer un tableau de donnée en un clin d'oeil.

Voici un cas d'exemple

SELECT ID_Project, ID_FormModel, [1],[2],[3],[4],[5],[6] FROM
(select FM.ID_Project, FM.ID_FormModel, FI.FormStatus, 1 AS cnt from tblDMFormModel FM
LEFT OUTER JOIN tblDMForminstance FI on FI.ID_FormModel=FM.ID_FormModel) AS RawData
PIVOT (
SUM(cnt) FOR FormSTATUS IN ([1],[2],[3],[4],[5],[6])
) pvt
WHERE ID_Project=165

La première ligne formatte le tableau en affichant les deux colonnes non pivotables : l'id_project et le form model, les valeurs en gras indique les valeur du status des formes.

La seconde ligne construit le tableau des données brutes sur laquelle nous allons appliquer le pivot. (Le left outer join sert à récupérer tous les form modèles, même ceux qui n'ont pas de form instance). 1 indique que le form instance apparait une fois.
PIVOT fait pivoter les valeurs de rows autours de FormStatus en sommant les form instance pour obtenir les comptes.

Voilà, c'est peut-être pas super bien expliqué mais ça marche !