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:
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.
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 ?
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
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.:
http://rixni.free.fr/tmp/tableau_croise_dynamique.jpg
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.
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).
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.
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