Bernard Fedotoff

Jour        Duree                  Nom

----------- ---------------------- --------------------

1           2,5                    Bernard

2           3                      Bernard

1           4                      Christine

3           6                      Christine

3           1,5                    Bernard

5           4                      Bernard
5           6                      Christine

En appliquant l’opérateur PIVOT suivant la requête suivante :

SELECT * FROM dbo.TestTemps

      PIVOT (sum(Duree) FOR Jour IN ([1], [2], [3], [4] )) as PVT

On obtient le jeu de données croisées suivant :

Nom             1      2      3      4      5

--------------- ------ ------ ------ ------ ------

Bernard         2,5    3      1,5    NULL   4

Christine       4      NULL   6      NULL   6

C’est donc un opérateur sur le principe très pratique pour croiser tout type de jeu d’enregistrements. Néanmoins, il possède un inconvénient majeur : la liste d’éléments explicités dans l’ensemble de l’opérateur IN est une liste finie qui ne peut pas être générée à partir d’une sous requête SELECT.

Se pose donc le problème de l’utilisation de PIVOT avec une liste d’éléments variables, comme par exemple le numéro de jour du mois donc le maximum vari de 28 à 31.

Voici une solution qui a été implémentée dans le cadre d’une gestion de suivi des temps passés par des ressources sur des projets (informatiques). Le principe de la solution est de générer dynamiquement une chaine de caractères qui représente l’ensemble à appliquer pour l’opérateur IN, et d’exécuter la requête avec une EXEC().

La modélisation (simplifiée) de la base de données est la suivante :

Tout d’abord, une table temporaire contenant l’ensemble des numéros de jour pour un mois donné et ayant des saisies de temps :

DECLARE @NumeroDeMois int ,@NumeroDeProject int

SET @NumeroDeMois = 1

DECLARE @ListeDesJours TABLE (IdJour int )

INSERT INTO @ListeDesJours (IdJour)

   SELECT DISTINCT  DAY(Date) FROM Temps

      WHERE MONTH(Date) = @NumeroDeMois

Puis une chaine de caractère est créée sur le format suivant ‘[1], [2], [3], …’ en balayant l’ensemble des enregistrements de la table temporaire :

DECLARE @Jours varchar(2000)

SET @Jours = ''

SELECT @Jours = @Jours + '[' + convert(varchar(2),IdJour) + '],'

FROM @ListeDesJours

Le dernier ‘,’ est retiré de la chaine de caractères :

SET @Jours = LEFT(@Jours, LEN(@Jours) - 1)

Enfin la requête PIVOT est exécutée par l’intermédiaire d’une EXEC(), en référençant la chaîne créée :

SET         @NumeroDeProjet = 1

EXEC( '

DECLARE @RecapitulatifDesTemps TABLE

(     Nom        varchar(50)

      ,IdJour           int

      ,Duree            float

)

INSERT INTO @RecapitulatifDesTemps

(

       Nom       

      ,IdJour    

      ,Duree

)