Discussion :

[chaporon]

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.

[pifor]

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

[chaporon]

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 ?

[remi4444]

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...

[chaporon]

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

Bonne journée
Nico

[orafrance]

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

[chaporon]

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.

[orafrance]

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

[chaporon]

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).

[remi4444]

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...)

[chaporon]

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.

[Nakashima]

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