PC Easy Weblog

février 27, 2009

SQL Server – Tableau croisé oui, dynamique non

Filed under: Développement — Étiquettes : , — trucmuche92 @ 9:12

MS Access fournie une commande SQL qui permet de faire des requêtes SELECT de type tableau croisé dynamique (via la commande TRANSFORM). Dans ce cas, on est bien présence d’un tableau dynamiqe car le nombre de colonnes généré est dynamique en fonction des enregistrements du jeu de résultat.

Pour SQL Server, depuis sa version 2005, on a la syntaxe PIVOT qui permet de créer un tableau croisé à partir d’une table, en revanche pour ce qui est du dynamique, c’est pas encore ça.

En effet, il faut préciser les valeurs que prendront les colonnes, illustration :

On a une table cde_entete

3 champs-propriété : cde_liv_civilite, cde_status, cde_mode_liv

un champs numérique : cde_mnt_ht (celui qui aura l’agregat)

On veut un tableau croisé avec les données de la colonne « Mode livraison » (cde_mode_liv) en colonne :

SELECT
cde_liv_civilite,
cde_status,
SUM([1]) AS "avion",
SUM([2]) AS "train"
FROM  cde_entete
PIVOT ( SUM(cde_mnt_ht)
        FOR cde_mode_liv IN ([1], [2])
      ) AS xxx
GROUP BY cde_liv_civilite, cde_status

Pour comprendre, il faut prendre la syntaxe ci-dessus comme un SELECT traditionnel où xxx transforme la table cde_entete avec les valeurs du champ cde_mode_liv en tant que colonne.

Le blème, c’est que ces valeurs doivent être explicitement définies « IN ([1], [2])« , et c’est là qu’on perds l’aspect dynamique.

En revanche, là où ça peut être interressant c’est quand veut un resultset d’indicateurs très variés pour lesquels il nécessiterait d’effectuer de multiple jointures.
L’astuce résiderait à créer un resultset par une série de requêtes UNION dont 2 colonnes pivoteraient au final.

1 – Obtention d’indicateurs et de dimensions depuis plusieurs sourcing

        --1ere sourcing
        select
            year(dt_record) as ANNEE,
            'EMPLOYE' as DIMENSION,
            count(cd_employee) as VALEUR
            from RESOURCES group by year(dt_record)

    UNION
        --2ème sourcing
        select
            year(dt_planning) as ANNEE,
            'BUDGET' as DIMENSION,
            sum (AMOUNT) as VALEUR
            from BUD group by year(dt_planning)

    UNION
        --3ème sourcing
        select
            year(dt_sale) as ANNEE,
            'CA' as DIMENSION,
            sum (SALES) as VALEUR
            from PRODUCTS group by year(dt_sale)

2 – Le résultat de l’UNION sans le PIVOT :

ANNEE       DIMENSION VALEUR
----------- --------- -----------
2006        BUDGET    50000
2007        BUDGET    55000
2008        BUDGET    60000
2005        CA        100000
2006        CA        110000
2007        CA        55
2007        CA        66
2007        CA        120000
2005        EMPLOYE   80
2006        EMPLOYE   86
2007        EMPLOYE   90
2008        EMPLOYE   91

3 – En passant le PIVOT comme ceci…

SELECT
ANNEE,
SUM([EMPLOYE]) AS "Nbr Employés",
SUM([BUDGET]) AS "Budget planifié",
SUM([CA]) AS "Chiffre d'affaire"
FROM (

        --1ere sourcing
        select
            year(dt_record) as ANNEE,
            'EMPLOYE' as DIMENSION,
            count(cd_employee) as VALEUR
            from RESOURCES group by year(dt_record)

    UNION
        --2ème sourcing
        select
            year(dt_planning) as ANNEE,
            'BUDGET' as DIMENSION,
            sum (AMOUNT) as VALEUR
            from BUD group by year(dt_planning)

    UNION
        --3ème sourcing
        select
            year(dt_sale) as ANNEE,
            'CA' as DIMENSION,
            sum (SALES) as VALEUR
            from PRODUCTS group by year(dt_sale)

) MES_UNIONS
PIVOT ( SUM(VALEUR)
        FOR DIMENSION IN ([EMPLOYE], [BUDGET], [CA])
      ) AS bidon
GROUP BY ANNEE

4 – Résultat final :

ANNEE       Nbr Employés Budget planifié Chiffre d'affaire
----------- ------------ --------------- -----------------
2005        80                           100000
2006        86           50000           110000
2007        90           55000           120121
2008        91           60000

Cette méthode est très utile pour du reporting par exemple, et évite d’avoir recours à de multiples jointure externes.

Publicités

2 commentaires »

  1. relationship books for alpha women characteristics

    SQL Server – Tableau croisé oui, dynamique non | PC Easy Weblog

    Rétrolien par relationship books for alpha women characteristics — décembre 21, 2014 @ 8:52


RSS feed for comments on this post. TrackBack URI

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

Créez un site Web ou un blog gratuitement sur WordPress.com.

%d blogueurs aiment cette page :