[SQL Server] Requête complexe sans table temporaire : possible?

Version imprimable

28/01/2007, 22h10
Thi0123

[SQL Server] Requête complexe sans table temporaire : possible?

Bonjour,

Je travaille sur un programme de gestion de projet sous access et sql server.

J'ai 5 tables :

- une table employé contenant les noms et infos des employés
- une table grade contenant les noms des grades
- une table projet contenant les différents projets
- une table affectation_grade permettant d'affecter un grade à un employé
- une table affectation_projet permettant d'affecter un projet à un employé

A partir de ces tables je dois générer une requête m'affichant pour chaque projet et pour chaque grade, le nombre de personne affectée sur le projet et sur ce grade.

Le problème est que les colonnes des grades ne sont pas fixes, si jamais j'ajoute un nouveau grade, il faut que mon tableau ajoute automatiquement la colonne. Pareil pour les projets, si j'ai 10 projets j'aurai donc plus de lignes.

http://img169.imageshack.us/img169/3457/resultatgk0.png

J'avais commencé à créer une table temporaire avec du code vba pour me créer la table dynamiquement puis insérer les données avec autant de requete select count qu'il y a de grade et de projet mais on m'a demandé d'abandonner cette solution ET d'utiliser des vues à la place.

Est ce que c'est possible de faire ça uniquement avec des vues?
Merci d'avance pour votre aide
28/01/2007, 22h56
Antoun

Access te permet de faire une requête de type "Analyse croisée". Si tu y places tes grades en Colonnes, tes projets en Lignes et un Compte d'employés en Valeurs, tu as ta requête, moins les ligne et colonne de total.

PS : pour la colonne de total, tu fais une requête à part, faisant les totaux par projet, et tu la jointures ensuite avec la requête analyse croisée.

Pour la ligne de total, tu fais à nouveau une requête analyse croisée, sans rien en Lignes, et tu fais une Union avec le paquet ci-dessus.

Voici maintenant la solution en pur SQL (pour faire une vue dans SQL Server). La contrainte est que tu dois connaître à l'avance la liste de tes colonnes ; autrement dit, si un nouveau grade apparaît, il faudra que tu modifies ta vue.
Code:

1 2 3 4 5 6 7 SELECT Projet, SUM(CASE WHEN IDgrade = 1 THEN 1 ELSE 0 END) AS Grade1, SUM(CASE WHEN IDgrade = 2 THEN 1 ELSE 0 END) AS Grade2, SUM(CASE WHEN IDgrade = 3 THEN 1 ELSE 0 END) AS Grade3, COUNT(*) AS Total FROM... GROUP BY Projet
Pour la ligne de total, tu peux faire un ROLLUP si ta version de SQL Server le permet ; sinon, tu ajoutes une seconde requête en Union :
Code:

1 2 3 4 5 6 7 8 9 ... UNION ALL SELECT 'Total projets', SUM(CASE WHEN IDgrade = 1 THEN 1 ELSE 0 END) AS Grade1, SUM(CASE WHEN IDgrade = 2 THEN 1 ELSE 0 END) AS Grade2, SUM(CASE WHEN IDgrade = 3 THEN 1 ELSE 0 END) AS Grade3, COUNT(*) AS Total FROM... -- pas de GROUP BY

29/01/2007, 11h54
Thi0123

Un grand merci pour avoir regardé :)
Pour la première solution je ne peux pas l'utiliser vu que je suis connecté à sql serveur. Ca aurait pu marcher si la base était dans access directement mais la c'est pas possible, il ne me propose pas l'option :?
Pour la deuxième solution je peux pas me permettre de faire ça, ça a l'air pas mal mais il faut que la vue se mette à jour toute seule lorsque je rajoute des grades.
29/01/2007, 13h29
Antoun

Citation:

Envoyé par Thi0123

Un grand merci pour avoir regardé :)
Pour la première solution je ne peux pas l'utiliser vu que je suis connecté à sql serveur. Ca aurait pu marcher si la base était dans access directement mais la c'est pas possible, il ne me propose pas l'option :?

Normalement il devrait te le proposer quand même (dans l'écran de requête, Menu Requête / Analyse croisée).

Citation:

Envoyé par Thi0123

Pour la deuxième solution je peux pas me permettre de faire ça, ça a l'air pas mal mais il faut que la vue se mette à jour toute seule lorsque je rajoute des grades.

Dans ce cas c'est la même solution, mais il te faut une proc stock T-SQL qui lise la table des grades et rédige dynamiquement la requête ci-dessus.
29/01/2007, 15h19
Thi0123

J'ai pas trouvé l'option, il me propose plein de choses comme créer une vue ou une procédure stockée ou une fonction mais pas de requête croisée comme avant.

Pour l'histoire de la procédure stockée, je fais une fonction qui me retourne ma liste des champs dans un curseur et dans une autre procédure je crée la requête en bouclant sur la première?

- pour Access : c'est parce que tu dois être dans un .mdp ; si tu fais un bête .mdb (avec des tables liées vers SQL Server), tu auras l'analyse croisée (par contre les perfs seront dégueulasses)

- en général : n'importe quel outil de reporting te fais un tableau croisé en 5 minutes... avec SQL Server, tu as sans doute Reporting Services ?

- pour SQL Server : un seul curseur/boucle suffit (c'est déjà trop*)

en gros, ça donne ça :
Code:

1 2 3 4 5 6 7 8 @sql = 'SELECT Projet' curseur ramenant @IDgrade et @LibGrade boucle sur ton curseur @sql = @sql + ', SUM(CASE WHEN IDgrade =' + @IDgrade + ' THEN 1 ELSE 0 END) AS ' + @LibGrade retour boucle @sql = @sql + 'reste de la requête' EXEC @sql

29/01/2007, 19h00
Antoun

* L'astuce 2 sur http://sqlpro.developpez.com/cours/s...r_avoidCursor/ te permet même d'éviter ce curseur-là
29/01/2007, 19h08
Thi0123

Merci beaucoup c'est vraiment sympa de m'aider! :)
J'ai presque réussi avec ta méthode, j'arrive pour l'instant à lister pour chaque projet et chaque grade le nombre de personne qu'il y a dessus :) Il me manque juste la ligne total mais j'ai pas encore essayé de faire le union ALL... je sens que ça va marcher! :mouarf: J'essayerai ça demain puisque j'ai pas sql server chez moi
29/01/2007, 20h34
Antoun

Citation:

Envoyé par Thi0123

Merci beaucoup c'est vraiment sympa de m'aider! :)
J'ai presque réussi avec ta méthode, j'arrive pour l'instant à lister pour chaque projet et chaque grade le nombre de personne qu'il y a dessus :)

ça me fait plaisir de savoir que ça marche, ça fait des années que je connais ce truc-là sans jamais avoir eu l'occasion d'essayer !

Citation:

Envoyé par Thi0123

Il me manque juste la ligne total mais j'ai pas encore essayé de faire le union ALL...

avant de te lancer dans l'UNION, essaie juste de faire ton GROUP BY comme ça :

Code:

GROUP BY Projet WITH ROLLUP
30/01/2007, 09h10
Thi0123

ROLLUP fonctionne pour le moment sous Sql server 2005 mais je vais bientot repasser en version 2000, j'espère que ça marchera toujours.

Sinon tu sais s'il est possible d'afficher "Total" à gauche de la ligne qui fait le total de chaque colonne? Car la ça fait bizarre j'ai une colonne total tout à droite pour le total de chaque projet mais une ligne sans libellé qui fait le total de chaque grade ;)

Edit : ça fonctionne avec deux requêtes et le union all
30/01/2007, 12h22
Antoun
Citation:

Envoyé par Thi0123

Sinon tu sais s'il est possible d'afficher "Total" à gauche de la ligne qui fait le total de chaque colonne?
Code:

1 2 SELECT COALESCE(Projet, 'Total'), LibGrade1, LibGrade2, ... FROM (tout le bouzin)
30/01/2007, 13h37
Thi0123

C'est impeccable, ça marche super bien maintenant! Ca marchait déja avec la seconde requête mais c'est encore mieux la :D Je connaissais pas COALESCE, si je comprends bien ça remplace les cases vides par le nom qu'on lui passe entre parenthèse :lol:

Merci beaucoup pour ton aide 8-) Je pense pas que j'aurai réussi sans toi j'avais passé plusieurs journées à chercher sur google mais sans succès.