1. #1
    Membre du Club
    Homme Profil pro
    Consultant en Business Intelligence
    Inscrit en
    mars 2006
    Messages
    46
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : Pays-Bas

    Informations professionnelles :
    Activité : Consultant en Business Intelligence
    Secteur : Conseil

    Informations forums :
    Inscription : mars 2006
    Messages : 46
    Points : 45
    Points
    45

    Par défaut SQL : Création d'un tableau croisé dynamique

    J'aimerais directement créé un tableau croisé dynamique à partir d'une instruction SQL.

    J'ai cherché dans le documentation Oracle et voici ce que j'ai trouvé.

    Exemple de tableau dynamique que j'aimerais réalisé. Il s'agit des ventes
    par pays et par canal de ventes (internet ou vente direct).

    Channel_______France______US_______Total
    Internet______ 9,597_______124,224___133,821
    Direct Sales___ 61,202______638,201___699,403
    Total_________70,799______762,425___833,224

    Il est aisé de remplir les champs de ce tableau hors sous total et total général avec un group by.

    ORACLE fournit avec la fonction GROUP BY, les instructions ROLLUP ou CUBE qui fournisse alors les sous totaux et les totaux directement.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT channels.channel_desc, countries.country_iso_code,
      TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$
    FROM sales, customers, times, channels, countries
    WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id AND
      sales.channel_id= channels.channel_id AND channels.channel_desc IN
      ('Direct Sales', 'Internet') AND times.calendar_month_desc='2000-09'
      AND customers.country_id=countries.country_id
      AND countries.country_iso_code IN ('US','FR')
    GROUP BY CUBE(channels.channel_desc, countries.country_iso_code);
    CHANNEL_DESC_______CO________SALES$
    ______________________________833,224
    ____________________FR________70,799
    ____________________US________762,425
    Internet_______________________133,821
    Internet_____________FR________9,597
    Internet_____________US________124,224
    Direct Sales____________________699,403
    Direct Sales__________FR________61,202
    Direct Sales__________US_______638,201

    C'est génial sauf que la plupart de mes clients ne demande pas cette mise en forme mais plutôt le premier tableau.

    D'ou ma question :
    Est-il possible via une instruction sql d'avoir directement le premier tableau sans passer par du PL/SQL.

    Plus d'info :
    http://download-uk.oracle.com/docs/c...g.htm#g1014032

    Merci d'avance.

    Ps.: Et si quelqu'un me dit comment saisir un tableau dans le forum, je suis preneur. Désolé pour les underscore mais c'est tout ce que j'ai trouvé pour aligner les champs.

  2. #2
    Membre expert

    Profil pro
    Inscrit en
    février 2006
    Messages
    3 435
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2006
    Messages : 3 435
    Points : 3 522
    Points
    3 522

    Par défaut

    On dit aussi pivot (en anglais aussi). Voir un exemple détaillé en anglais ici: http://asktom.oracle.com/pls/ask/f?p...A:124812348063

  3. #3
    Membre du Club
    Homme Profil pro
    Consultant en Business Intelligence
    Inscrit en
    mars 2006
    Messages
    46
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : Pays-Bas

    Informations professionnelles :
    Activité : Consultant en Business Intelligence
    Secteur : Conseil

    Informations forums :
    Inscription : mars 2006
    Messages : 46
    Points : 45
    Points
    45

    Par défaut Merci, réponse rapide mais ...

    Hello,

    Merci beaucoup pour cette réponse rapide qui me va droit au coeur. Je ne savais pas qu'un tableau croisé dynamqiue s'appelait pivot en anglais.

    Tom a l'air de dire qu'en SQL, il n'est pas possible de faire un pivot si on ne donne pas le nombre de colonne.

    Hors lors d'une expression GROUP BY CUBE par exemple, on ne connait pas le nombre de colonne (par exemple le nombre de pays) et pourtant il fait bien les totaux.
    Ca veut dire qu'Oracle fait une deuxième passe et je me demandais s'il n'y avait pas une commande SQL pour réaliser un pivot d'un GROUP BY CUBE.

    A aujourd'hui, je suis obligé de créer un SQL en définissant chaque colonne à la main (ou par une procédure PL/SQL) et de réaliser les totaux dans mon logiciel de reporting.

    En regardant l'expression GROUP BY CUBE, je pensais que je pouvais peut être réaliser tout cela en une seule expression SQL mais ce n'est toujours pas le cas. Quel est alors le gain de GROUP BY CUBE ?

  4. #4
    Membre éprouvé
    Inscrit en
    avril 2006
    Messages
    1 024
    Détails du profil
    Informations forums :
    Inscription : avril 2006
    Messages : 1 024
    Points : 1 292
    Points
    1 292

    Par défaut

    ce n'est pas tout à fait la même chose, la fonction CUBE te fait des calculs croisés mais la restitution se fait sur un nombre de colonnes bien fixé au départ.
    Toi tu voudrais que le nombre de colonnes d'un curseur soit décidé d'aprés le resultat d'une requête ce qui me semble difficile. La notion de tableau croisé est non seulement une problématique de calcul (ce qui est résolu par les groupements, cube, max, decode etc...) mais aussi une problématique d'affichage. Pour un logiciel comme Access, ça ne pose pas de problème puisque il y a tout mélangé dans un meme programme (présentation + moteur SQL). Mais le sql-plus n'est qu'un programme de présentation trés sommaire, donc je doute que ça soit possible de tout faire avec...

  5. #5
    Membre du Club
    Homme Profil pro
    Consultant en Business Intelligence
    Inscrit en
    mars 2006
    Messages
    46
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : Pays-Bas

    Informations professionnelles :
    Activité : Consultant en Business Intelligence
    Secteur : Conseil

    Informations forums :
    Inscription : mars 2006
    Messages : 46
    Points : 45
    Points
    45

    Par défaut Ok, ok mais je rêve d'un monde meilleur

    Merci pour toutes vos réponses, elles m'ont bien éclairé sur les limites du SQL.

    Bonne journée
    Nico

  6. #6
    Expert éminent sénior
    Avatar de orafrance
    Profil pro
    Inscrit en
    janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 39
    Localisation : France

    Informations forums :
    Inscription : janvier 2004
    Messages : 15 967
    Points : 19 027
    Points
    19 027

    Par défaut

    il est important de rappeler que le SQL n'est pas sensé ce soucier des problèmes de présentations

  7. #7
    Membre du Club
    Homme Profil pro
    Consultant en Business Intelligence
    Inscrit en
    mars 2006
    Messages
    46
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : Pays-Bas

    Informations professionnelles :
    Activité : Consultant en Business Intelligence
    Secteur : Conseil

    Informations forums :
    Inscription : mars 2006
    Messages : 46
    Points : 45
    Points
    45

    Par défaut Alors comment fait-on pour tableau croisé dynamique ?

    Je suis d'accord sur le fait que le SQL ne doit pas traiter des problèmes de présentation.

    Sauf qu'au niveau des tableaux croisées (ou pivot), trés peu d'outils de reporting savent gérer une présentation poussée.
    J'ai testé :
    - crystal report
    - oracle report writer
    - Business Object
    Et je ne peux pas sans avoir recours au sql obtenir ce genre de résultats :
    Ex.:


    La plupart du temps, on fournit les données par une instruction sql, on définit les colonnes et les lignes. Et malheureusement, on ne peut plus guère toucher à la présentation.

    Voila pourquoi je suis trés interessé pour réaliser un pivot en sql. Je me retrouve alors avec une table normale (les données sont en ligne et non en colonne) et les logiciels de reporting ont alors beaucoup plus d'options à offrir pour une présentation poussée.

    Si quelqu'un connait un logiciel de reporting qui gère à merveille les tableaux croisées dynamiques (ou pivot), je veux bien le connaître.

    Ou alors il existe peut être une manière de transformer un SELECT GROUP BY CUBE en pivot via un logiciel mais je la connais pas.

    Encore merci à tous pour vos réponses.

  8. #8
    Expert éminent sénior
    Avatar de orafrance
    Profil pro
    Inscrit en
    janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 39
    Localisation : France

    Informations forums :
    Inscription : janvier 2004
    Messages : 15 967
    Points : 19 027
    Points
    19 027

    Par défaut

    j'vais peut-être me faire conspuer... mais Excel ne serait-il pas adapté ?

  9. #9
    Membre du Club
    Homme Profil pro
    Consultant en Business Intelligence
    Inscrit en
    mars 2006
    Messages
    46
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : Pays-Bas

    Informations professionnelles :
    Activité : Consultant en Business Intelligence
    Secteur : Conseil

    Informations forums :
    Inscription : mars 2006
    Messages : 46
    Points : 45
    Points
    45

    Par défaut Excel - Un top de reporting croisé

    Non, non pas du tout ... pas de conspuation au contraire.

    Je l'utilise chez un des mes clients, je réalise des vues avec les données qu'il veut, je créé ensuite un lien vers des données externes en ODBC et il se fait
    ses tableaux croisées comme il veut.

    C'est une solution pas chère et super qui a remplacé une solution de cube existante car plébiscité par les utilisateurs.

    Mon problème est que mon tableau croisé dynamique est intégré à un logiciel sous forme d'un rapport (donc pas d'excel possible mais la solution est vraiment super).

  10. #10
    Membre éprouvé
    Inscrit en
    avril 2006
    Messages
    1 024
    Détails du profil
    Informations forums :
    Inscription : avril 2006
    Messages : 1 024
    Points : 1 292
    Points
    1 292

    Par défaut

    Citation Envoyé par chaporon
    Si quelqu'un connait un logiciel de reporting qui gère à merveille les tableaux croisées dynamiques (ou pivot), je veux bien le connaître.
    Business object le fait très bien , mais c'est pas vraiment un outil de reporting (on galère un peu sur les entêtes les mise en pages etc...). Je connais pas parfaitement Crystal mais il me semble bien que ce soit possible (d'après la doc au moins...)

    Mais y'a quand meme quelque chose que je voudrais faire remarqué c'est que l'exemple de rapport que tu donne ne ressemble pas du tout à un tableau croisé....

    Le nombre et les entêtes de colonnes on tout l'air d'être fixées à l'avance (avec le séparateur au milieu qui va bien et tout...). Et meme les colonnes ne contenant aucune donnée sont quand meme présentes. Ce qui caractérise un tableau croisé, c'est que le nombre et l'entete des colonnes dépendent des données, ça donnne donc un tableau de largeur aléatoire (ce qui pose d'ailleur des soucis de mise en page...)

  11. #11
    Membre du Club
    Homme Profil pro
    Consultant en Business Intelligence
    Inscrit en
    mars 2006
    Messages
    46
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : Pays-Bas

    Informations professionnelles :
    Activité : Consultant en Business Intelligence
    Secteur : Conseil

    Informations forums :
    Inscription : mars 2006
    Messages : 46
    Points : 45
    Points
    45

    Par défaut Exact, c'est un tableau croisé à moitié dynamique

    Et oui, c'est un tableau croisé à moitié dynamique car le nombre de colonne est fixe à aujourd'hui.

    Demain, j'aurais aimé avoir la possibilité de faire varier les colonnes en paramètre.
    Ex.de tableau croisé dynamique :
    Les ventes par produit et par région.

    Je peux regrouper par région de vente (le sud ouest). Le sud ouest comprend certains départements (33, 64, ...). Hors pour des raisons commercials que j'ignore encore, le directeur des ventes peut vouloir scinder la région sud ouest en deux : le sud sud ouest : le département 64 et le nord sud-ouest le 33.

    On peut faire la même manipulation par gamme de produit, par type de mouvement (ce qui est le cas du rapport au dessus, ...).

    Cette manipulation rajoute donc une colonne de plus au rapport et dans ce cas là, il faut refaire le sql et refaire le rapport.

    Dans tous les cas, il faudra refaire le rapport (comme tu le dis, à cause des soucis de mise en page) mais si je pouvais me passer de refaire le sql, je suis preneur.

    Car, si le SQL prend en compte toutes les colonnes en automatique, ça permet également rapidement de vérifier qu'il ne manque pas une colonne. Et c'est pratique pour vérifier différents rapports qui devraient avoir le même total :
    Ex. :
    Ventes par gamme ou ventes par région doivent avoir le même total.

    Pour ma part, je suis parti sur du SQL dynamique avec l'exemple de Tom donné plus haut par pifor (Encore merci ;-) et en plus, ça fonctionne en 8 sans les fonctions d'aggrégation).

    Bonne soirée à tous et encore merci.

  12. #12
    Candidat au Club
    Femme Profil pro
    Analyse système
    Inscrit en
    septembre 2016
    Messages
    1
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 25
    Localisation : Haïti

    Informations professionnelles :
    Activité : Analyse système
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : septembre 2016
    Messages : 1
    Points : 2
    Points
    2

    Par défaut Rapport tableau croise dynamique avec des graphes

    Bonjour je rédige un cahier de charge pour améliorer un système d'information que j'ai, ca a ete creer avec les langages de programmation utilisés : PHP, JavaScript,
    Framework utilisés: Laravel 4, JQuery, Bootstrap 3
    . je voudrais ajouter un rapport avec un tableau croisé dynamique. Je voudrais quelques exemples de forme s'il vous plait pour m'inspirer. L’idée se serait d'avoir des graphes très jolie avec un tableau contenant les informations.
    Vous pouvez m'envoyer de simple capture écran d'un modèle
    Merci d’advance

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

Discussions similaires

  1. [AC-2007] Création d'un Tableau Croisé Dynamique (TCD) Excel depuis VBA Access
    Par Largo38 dans le forum VBA Access
    Réponses: 0
    Dernier message: 17/07/2013, 12h36
  2. Création d'un tableau croisé dynamique avec base données variable
    Par div20 dans le forum Macros et VBA Excel
    Réponses: 3
    Dernier message: 14/01/2012, 23h43
  3. Réponses: 2
    Dernier message: 04/11/2010, 17h42
  4. Réponses: 4
    Dernier message: 31/07/2007, 14h34

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