IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Pivot - Compter les valeurs par colonnes


Sujet :

Développement SQL Server

  1. #1
    Membre éclairé

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    919
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : janvier 2010
    Messages : 919
    Points : 849
    Points
    849
    Billets dans le blog
    26
    Par défaut Pivot - Compter les valeurs par colonnes
    Bonjour à tous ,

    J'ai la structure de données suivante

    en colonne
    • Y
    • Y+1
    • Y+2


    Et pour chaque ligne les valeurs possibles suivantes pour chacune des colonnes
    • Actif
    • Désactivé
    • A modifié
    • Non traité



    Ce que je veux obtenir c'est un tableau de type


    Y Y+1 Y+2
    Actif Nbre Nbre Nbre
    Désactivé Nbre Nbre Nbre
    A modifié Nbre Nbre Nbre
    Non traité Nbre Nbre Nbre

    J'imagine bien passé par cross apply mais ça bloque pour avoir les statut en ligne ou en colonne
    Merci par avance
    Mal nommer un objet, c'est ajouter au malheur de ce monde, car le mensonge est justement la grande misère humaine, c'est pourquoi la grande tâche humaine correspondante sera de ne pas servir le mensonge
    Poésie 44, n° 17 - Albert Camus

    Mes réponses vous ont aidés, un clic sur leur pouce vert
    Bonjour chez vous

  2. #2
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Consultant Teradata
    Inscrit en
    septembre 2008
    Messages
    8 191
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Consultant Teradata
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : septembre 2008
    Messages : 8 191
    Points : 17 073
    Points
    17 073
    Par défaut
    Encore mieux que le cross apply, la fonction PIVOT.

  3. #3
    Membre éclairé

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    919
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : janvier 2010
    Messages : 919
    Points : 849
    Points
    849
    Billets dans le blog
    26
    Par défaut
    Bonjour Waldar,

    Ok pour la fonction pviot mais comment basculer les valeurs des champs Y a Y+X en ligne sachant que des valeurs peuvent être presentes dans Y+i et pas dans Y+j et ensuite avoir le nombre de valeurs par lignes/colonnes ?

    Par contre, la liste des valeurs sont dans une table

    Pas de partition by j'espère ?
    Mal nommer un objet, c'est ajouter au malheur de ce monde, car le mensonge est justement la grande misère humaine, c'est pourquoi la grande tâche humaine correspondante sera de ne pas servir le mensonge
    Poésie 44, n° 17 - Albert Camus

    Mes réponses vous ont aidés, un clic sur leur pouce vert
    Bonjour chez vous

  4. #4
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Consultant Teradata
    Inscrit en
    septembre 2008
    Messages
    8 191
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Consultant Teradata
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : septembre 2008
    Messages : 8 191
    Points : 17 073
    Points
    17 073
    Par défaut
    Je ne suis pas sûr d'avoir compris votre inquiétude, pouvez-vous créer un petit jeu de données (fictif mais représentant votre problème) pour voir ce qui vous ennuie ?

  5. #5
    Membre éclairé

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    919
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : janvier 2010
    Messages : 919
    Points : 849
    Points
    849
    Billets dans le blog
    26
    Par défaut
    Merci pour l'aide et j'ai joint un jeu de données
    Fichiers attachés Fichiers attachés
    Mal nommer un objet, c'est ajouter au malheur de ce monde, car le mensonge est justement la grande misère humaine, c'est pourquoi la grande tâche humaine correspondante sera de ne pas servir le mensonge
    Poésie 44, n° 17 - Albert Camus

    Mes réponses vous ont aidés, un clic sur leur pouce vert
    Bonjour chez vous

  6. #6
    Membre éclairé

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    919
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : janvier 2010
    Messages : 919
    Points : 849
    Points
    849
    Billets dans le blog
    26
    Par défaut
    En attendant, j'ai implémenté une solution de bourrin,

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT 0 as Pos, '[T]' as Period , label , IIf(t2.CountLabel Is Null,0,t2.CountLabel) AS CountLabel
    FROM (SELECT  t1.Y,  count( t1.Y) AS CountLabel
    FROM (SELECT DISTINCT t.FIELD1  , t.Y FROM dbo_TABLE1t) t1
    GROUP BY Y)  AS t2 RIGHT JOIN table2 ON t2.Y = table2.label
    union
    SELECT 1 as Pos, '[T+1]' as Period , label , IIf(t2.CountLabel Is Null,0,t2.CountLabel) AS Expr2
    FROM (SELECT  t1.[Y+1],  count( t1.[Y+1]) AS CountLabel
    FROM (SELECT DISTINCT t.FIELD1  , t.[Y+1] FROM dbo_TABLE1t) t1
    GROUP BY [Y+1])  AS t2 RIGHT JOIN table2 ON t2.[Y+1] = table2.label
    UNION ....
    Mal nommer un objet, c'est ajouter au malheur de ce monde, car le mensonge est justement la grande misère humaine, c'est pourquoi la grande tâche humaine correspondante sera de ne pas servir le mensonge
    Poésie 44, n° 17 - Albert Camus

    Mes réponses vous ont aidés, un clic sur leur pouce vert
    Bonjour chez vous

  7. #7
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Consultant Teradata
    Inscrit en
    septembre 2008
    Messages
    8 191
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Consultant Teradata
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : septembre 2008
    Messages : 8 191
    Points : 17 073
    Points
    17 073
    Par défaut
    Merci c'est plus clair avec le fichier d'exemple.

    Données
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    create table MaTable
    ( ID      integer
    , [Y]     varchar(15)
    , [Y+1]   varchar(15)
    , [Y+2]   varchar(15)
    , [Y+3]   varchar(15)
    );
     
    insert into MaTable (ID, [Y], [Y+1], [Y+2], [Y+3]) values
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'NOT UNDER WATCH', 'NOT UNDER WATCH', 'NOT UNDER WATCH', 'NOT UNDER WATCH'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'NOT UNDER WATCH', 'NOT UNDER WATCH', 'NOT UNDER WATCH', 'NOT UNDER WATCH'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF'),
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF');
    Requête
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    with cte_unpivot (ID, Statut, Annee) as
    (
     select ID, Statut, Annee
       from MaTable
    unpivot (Statut FOR Annee IN ([Y], [Y+1], [Y+2], [Y+3])) as upv
    )
    select Statut, [Y], [Y+1], [Y+2], [Y+3]
      from cte_unpivot
     pivot (count(ID) for Annee in ([Y], [Y+1], [Y+2], [Y+3])) as pvt;
     
    Statut            Y  Y+1  Y+2  Y+3
    ---------------  --  ---  ---  ---
    ACTIF            25   25   25   25
    NOT UNDER WATCH   2    2    2    2

  8. #8
    Membre éclairé

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    919
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : janvier 2010
    Messages : 919
    Points : 849
    Points
    849
    Billets dans le blog
    26
    Par défaut
    beaucoup Waldar et pour le code qui va me faire gagner un temps précieux en terme de maintenance

    Peux-tu juste me confirmer ma compréhension du code
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
     
    select ID, Statut, Annee
       from MaTable
    unpivot (Statut FOR Annee IN ([Y], [Y+1], [Y+2], [Y+3])) as upv
    Ça génère le produit cartésien Nombre de lignes x Nombre de colonnes en passant le nom de la colonne comme valeur de Année -> unoivot por faire passer des colonnes en lignes
    (1024, 'ACTIF', 'ACTIF', 'ACTIF', 'ACTIF')
    1024 ACTIF Y
    1024 ACTIF Y+1
    1024 ACTIF Y+2
    1024 ACTIF Y+3
    1024, 'NOT UNDER WATCH', 'NOT UNDER WATCH', 'NOT UNDER WATCH', 'NOT UNDER WATCH'),
    1024 NOT UNDER WATCH Y
    1024 NOT UNDER WATCH Y+1
    1024 NOT UNDER WATCH Y+2
    1024 NOT UNDER WATCH Y+3"
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    select Statut, [Y], [Y+1], [Y+2], [Y+3]
      from cte_unpivot
     pivot (count(ID) for Annee in ([Y], [Y+1], [Y+2], [Y+3])) as pvt;
    Rebasculeur les valeurs du champ Années en colonne sde Y a Y+3 avec comme valeur pour chaque statut le nombre de ligne pour chaque valeur d'année -> Pivot pour faire passer des lignes en colonnes

    Statut Y Y+1 Y+2 Y+3
    --------------- -- --- --- ---
    ACTIF 25 25 25 25
    NOT UNDER WATCH 2 2 2 2


    Pour bien comprendre le code voici ça décomposition

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
    IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2
     
     select ID, StatusCode, Annee
       into #t from TableRef
    unpivot (StatusCode FOR Annee IN ([Y], [Y+1], [Y+2], [Y+3])) as upv
     
    select StatusCode, [Y], [Y+1], [Y+2], [Y+3] INTO #t2
      from #t 
     pivot (count(ID) for Annee in ([Y], [Y+1], [Y+2], [Y+3])) as pvt;
     
     select * from #t2


    Par contre cette ligne de commande reste assez nébuleuse !

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Le with cte_unpivot (ID, Statut, Annee) as
    Visiblement cte_unpiot correspond a une table temporaire. Pourrais-tu aussi me préciser la différence avec #table ?

    encore Waldar
    Mal nommer un objet, c'est ajouter au malheur de ce monde, car le mensonge est justement la grande misère humaine, c'est pourquoi la grande tâche humaine correspondante sera de ne pas servir le mensonge
    Poésie 44, n° 17 - Albert Camus

    Mes réponses vous ont aidés, un clic sur leur pouce vert
    Bonjour chez vous

  9. #9
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Consultant Teradata
    Inscrit en
    septembre 2008
    Messages
    8 191
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Consultant Teradata
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : septembre 2008
    Messages : 8 191
    Points : 17 073
    Points
    17 073
    Par défaut
    Citation Envoyé par informer Voir le message
    Ça génère le produit cartésien Nombre de lignes x Nombre de colonnes en passant le nom de la colonne comme valeur de Année -> unoivot por faire passer des colonnes en lignes
    C'est bien ça mais ce n'est pas un produit cartésien, c'est plutôt un UNION ALL de toutes les colonnes (c'est l'ancienne méthode pour faire un UNPIVOT).

    Citation Envoyé par informer Voir le message
    Rebasculeur les valeurs du champ Années en colonne sde Y a Y+3 avec comme valeur pour chaque statut le nombre de ligne pour chaque valeur d'année -> Pivot pour faire passer des lignes en colonnes
    Absolument !


    Citation Envoyé par informer Voir le message
    Par contre cette ligne de commande reste assez nébuleuse !
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Le with cte_unpivot (ID, Statut, Annee) as
    C'est une expression de table commune, ou encore common table expression (CTE) chez nos amis anglophones.
    Ca permet de déclarer une vue à la volée, le temps de la requête.
    C'est pratique lorsqu'on a des grosses requêtes avec beaucoup d'imbrication car on les déclare 'en haut' et de manière plus lisible qu'avec x niveau d'indentation, et on peut les réutiliser plusieurs fois dans la même requête.
    C'est comme si j'avais écris ceci :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    select Statut, [Y], [Y+1], [Y+2], [Y+3]
      from ( select ID, Statut, Annee
               from MaTable
            unpivot (Statut for Annee in ([Y], [Y+1], [Y+2], [Y+3])) as upv
           ) as cte_unpivot (ID, Statut, Annee)
     pivot (count(ID) for Annee in ([Y], [Y+1], [Y+2], [Y+3])) as pvt;

  10. #10
    Membre éclairé

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    919
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : janvier 2010
    Messages : 919
    Points : 849
    Points
    849
    Billets dans le blog
    26
    Par défaut
    Citation Envoyé par Waldar Voir le message

    C'est une expression de table commune, ou encore common table expression (CTE) chez nos amis anglophones.
    Ca permet de déclarer une vue à la volée, le temps de la requête.
    C'est pratique lorsqu'on a des grosses requêtes avec beaucoup d'imbrication car on les déclare en haut' et de manière plus lisible qu'avec x niveau d'indentation, et on peut les utiliser plusieurs fois dans la même requête.
    Waldar,

    Comme tu écris
    on les déclare 'en haut'
    Peux tu me préciser :
    S'il est possible par conséquent d'en déclarer plusieurs
    Comment se déclarent elles?
    Quelles différences avec des tables temporaires déclarées?

    Encore merci pour ton aide précieuse.
    Mal nommer un objet, c'est ajouter au malheur de ce monde, car le mensonge est justement la grande misère humaine, c'est pourquoi la grande tâche humaine correspondante sera de ne pas servir le mensonge
    Poésie 44, n° 17 - Albert Camus

    Mes réponses vous ont aidés, un clic sur leur pouce vert
    Bonjour chez vous

  11. #11
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Consultant Teradata
    Inscrit en
    septembre 2008
    Messages
    8 191
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Consultant Teradata
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : septembre 2008
    Messages : 8 191
    Points : 17 073
    Points
    17 073
    Par défaut
    Je vous renvoie sur le cours de SQLPro :
    https://sqlpro.developpez.com/cours/...cursives/#LIII

    On peut en déclarer plusieurs (je ne sais pas s'il y a une limite particulière) comme ceci :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    with cte1 (col1, ..., colA) as ( < une requête> )
       , cte2 (colZ, ..., colG) as ( < une requête> )
    ...
       , ctey (colSD, ..., colUSD) as ( < une requête> )
    <le select final>;
    Surtout elles peuvent s'appeler les unes les autres (enfin il en faut quand même une qui commence).

    Contrairement aux tables temporaires, elles font partie de la requête.
    Pour reprendre votre exemple plus faut, vous avez envoyé trois requêtes à la base de données (#t1, #t2, et la sélection finale), je n'en ai envoyé qu'une seule.
    Une fois la requête terminée, elles n'existent plus. Si vous voulez les réutiliser à nouveau, il faut les redéclarer et les réévaluer.

  12. #12
    Membre éclairé

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    919
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : janvier 2010
    Messages : 919
    Points : 849
    Points
    849
    Billets dans le blog
    26
    Par défaut
    Encore merci waldar pour toutes ces infos
    Mal nommer un objet, c'est ajouter au malheur de ce monde, car le mensonge est justement la grande misère humaine, c'est pourquoi la grande tâche humaine correspondante sera de ne pas servir le mensonge
    Poésie 44, n° 17 - Albert Camus

    Mes réponses vous ont aidés, un clic sur leur pouce vert
    Bonjour chez vous

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [XL-365] Tableau croisé dynamique - Compté les valeurs par colonnes
    Par informer dans le forum Conception
    Réponses: 10
    Dernier message: 11/10/2021, 17h06
  2. [XL-2000] regrouper et compter les valeurs identiques d'une colonne excel
    Par noemieze dans le forum Excel
    Réponses: 2
    Dernier message: 24/04/2014, 12h24
  3. Réponses: 4
    Dernier message: 04/08/2011, 13h22
  4. Réponses: 9
    Dernier message: 19/09/2008, 17h26
  5. requete pour compter les valeurs ds une colonne
    Par smariteau dans le forum Requêtes
    Réponses: 2
    Dernier message: 10/02/2006, 17h37

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo