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

SQL Server – Le cas du Case pour une comparaison

Filed under: Développement — Étiquettes : , — trucmuche92 @ 7:48

En SQL Server, l »utilisation de la syntaxe Case … When … When…else…end est bien utile quand on veut inclure un test dans une requête SELECT :

Exemple :

select nom, prenom, 
case civ 
when 1 then 'Monsieur'
when 2 then 'Madame'
else 'Non mentionné' end as civilite
from employes

Il arrive parfois que le test a effectuer ne s’appuie pas sur une égalité, mais sur une comparaison, la syntaxe est particulière et est la suivante :

select nom, prenom,
case 
        when LastActivityDate < '2008-01-01' then 'Très Longtemps'
        when LastActivityDate < '2008-07-01' then 'Longtemps'
        else 'Récement' end as "Dernère visite"
from aspnet_users

En fait la syntaxe peut paraître différente, mais pas pour tout le monde car les 2 commandes suivantes sont les mêmes et sont valides :

select nom, prenom, case civ 
                    when 1 then 'Monsieur'
                    when 2 then 'Madame'
                    else 'Non mentionné' 
                    end as civilite
from employes

select nom, prenom, case
                    when civ=1 then 'Monsieur' 
                    when civ=2 then 'Madame' 
                    else 'Non mentionné'
                    end as civilite
from employes

février 7, 2009

Fences – Organiser son bureau

Filed under: Utilitaires — Étiquettes : — trucmuche92 @ 10:17

stardockPour ceux comme moi qui utilisent beaucoup leur bureau, Fences est un soft de Stardock qui permet d’organiser le bureau en plusieurs blocs éditables.

fences

On peux resizer les blocs, les faire glisser, modifier le look, c’est pas mal du tout, j’hadère illico.

Matez une vidéo démo ici

février 2, 2009

Ajax – Animation de traitement serveur

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

preloaders_net

Preloader est un petit site qui permet de concevoir rapidement des animations de traitement du genre Ajax à partir de quelques modèles définis.

Quelques exemples :

21243639

Les modèles sont plutôt chouette, à bookmarker !!

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