SQL SERVER 2005 Pb de Pivot

Version imprimable

Bonjour,

Je dois ecrire une requete pour afficher le chiffre d'affaire d'une société, la colonne pivot est le mois.
J'ai une erreur dans la requete mais je ne comprend pas pourquoi ?

voici le code de la requete :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT t_cuno,t_nama,(SommeDet_amnt),MOIS
FROM
(SELECT     dbo.ttdsls045100.t_cuno, dbo.ttccom010100.t_nama, DATENAME(month,t_ddat) AS MOIS,Sum(dbo.ttdsls045100.t_amnt) AS SommeDet_amnt
FROM         dbo.ttdsls045100 INNER JOIN
                      dbo.ttiitm001100 ON dbo.ttdsls045100.t_item = dbo.ttiitm001100.t_item INNER JOIN
                      dbo.ttccom010100 ON dbo.ttdsls045100.t_cuno = dbo.ttccom010100.t_cuno
WHERE     (dbo.ttdsls045100.t_item < 'C ' OR
                      dbo.ttdsls045100.t_item > 'CZZ ' OR
                      dbo.ttdsls045100.t_item BETWEEN 'CDR' AND 'CDRZZ') AND 
					dbo.ttdsls045100.t_ddat BETWEEN convert(DATETIME, '01-01-2011 00:00:00',103) AND convert(DATETIME, '31-12-2011 00:00:00',103) AND 
                      (dbo.ttiitm001100.t_citg BETWEEN 'Z00000' AND 'Zzzzzz' OR
                      dbo.ttiitm001100.t_citg BETWEEN 'A00000' AND 'Azzzzz' OR
                      dbo.ttiitm001100.t_citg BETWEEN 'F00800' AND 'F00899')
GROUP BY dbo.ttdsls045100.t_cuno, dbo.ttccom010100.t_nama,DATENAME(month,t_ddat)) as tableSource
PIVOT ( SUM(SommeDet_amnt) FOR DATENAME(month,t_ddat) IN ([janvier],[fevrier],[mars]))as CA

le code de l'erreur est :

Code:

1
2
Msg 170, Level 15, State 1, Line 15
Ligne 15 : syntaxe incorrecte vers 'PIVOT'.

Un peu d'aide serai bienvenu.

Cordialement

08/06/2011, 08h06
elsuket

Bonjour,

C'est parce que l'opérateur PIVOT n’existe pas dans SQL Server 2005.
Il a été implémenté dans SQL Server 2008.

Néanmoins, le mieux est d’effectuer cela côté applicatif, puisque ce n'est pas un opérateur ensembliste.

@++ ;)

Bonjour,

Merci pour ta réponse, mais je ne suis pas d'accord, l'opérateur existe bien en SQL 2005, voici le lien MSDN

http://msdn.microsoft.com/fr-fr/libr...v=SQL.90).aspx

et un petit exemple pour voir :
Code:

1 2 3 4 5 6 7 8 SELECT Produit, SUM([2003]) AS [2003], SUM([2004]) AS [2004], SUM([2005]) AS [2005], SUM([2006]) AS [2006] FROM Ventes PIVOT (SUM(Vente) FOR annee IN ([2003], [2004], [2005], [2006])) AS Annees GROUP BY Produit
Le résultat :
Code:

1 2 3 A 9000 10000 12000 12500 B NULL 18000 NULL 15000 C 1100 850 1000 NULL
Pour revenir à mon probléme, n'est-il pas plus simple d'utiliser la methode lourde qui utilise les sous requetes pour chaque mois ??

A+

08/06/2011, 09h18
iberserk

Citation:

C'est parce que l'opérateur PIVOT n’existe pas dans SQL Server 2005

Le PIVOT a été introduit dans SQL SERVER 2005... enfin en France, BANGKOK je sais pas :-)

Un petit tuto sympas de fadace:
http://fadace.developpez.com/mssql/pivot/

bonjour

Citation:

Envoyé par tchraad

et un petit exemple pour voir :

exemple dans lequel PIVOT se trouve après la clause FROM, et non après la clause GROUP BY... ;)

que donne ceci (je n'ai pas lu la requete en entier...)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SELECT t_cuno,t_nama,(SommeDet_amnt),MOIS
FROM
(SELECT     dbo.ttdsls045100.t_cuno, dbo.ttccom010100.t_nama, DATENAME(month,t_ddat) AS MOIS,Sum(dbo.ttdsls045100.t_amnt) AS SommeDet_amnt
FROM         dbo.ttdsls045100 INNER JOIN
                      dbo.ttiitm001100 ON dbo.ttdsls045100.t_item = dbo.ttiitm001100.t_item INNER JOIN
                      dbo.ttccom010100 ON dbo.ttdsls045100.t_cuno = dbo.ttccom010100.t_cuno
PIVOT ( SUM(SommeDet_amnt) FOR DATENAME(month,t_ddat) IN ([janvier],[fevrier],[mars]))AS CA
WHERE     (dbo.ttdsls045100.t_item < 'C ' OR
                      dbo.ttdsls045100.t_item > 'CZZ ' OR
                      dbo.ttdsls045100.t_item BETWEEN 'CDR' AND 'CDRZZ') AND 
					dbo.ttdsls045100.t_ddat BETWEEN convert(DATETIME, '01-01-2011 00:00:00',103) AND convert(DATETIME, '31-12-2011 00:00:00',103) AND 
                      (dbo.ttiitm001100.t_citg BETWEEN 'Z00000' AND 'Zzzzzz' OR
                      dbo.ttiitm001100.t_citg BETWEEN 'A00000' AND 'Azzzzz' OR
                      dbo.ttiitm001100.t_citg BETWEEN 'F00800' AND 'F00899')
GROUP BY dbo.ttdsls045100.t_cuno, dbo.ttccom010100.t_nama,DATENAME(month,t_ddat)) AS tableSource

08/06/2011, 11h39
elsuket

OK, je prend zéro ... Désolé pour la boulette.

J'ai rarement utilisé PIVOT, tellement qu'en fait je n'ai du l'utiliser que sous SQL Server 2008.

ça ne change pas que cela ne devrait pas être fait côté base de données, puisque c'est de la présentation ...

@++ ;)

J'ai un léger doute sur le fait de pouvoir utiliser une express telle que DATENAME(month,t_ddat) plutôt qu'une colonne pour le pivot.
De toute façon votre problème est clair : vous devez "encapsuler" (surtout avec un group by).

Faites :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT .... FROM ( SELECT ...... AS ColonneQuiMinteresseVraiment1 -- Somme de la variance du total des achats partiellement impayées le samedi , ...... AS ColonneQuiMinteresseVraiment2 -- Encore pire , ...... AS ColonneQuiMinteresseVraiment3 -- J'en parle même pas FROM ( <query de ouf> ) AS X ) AS X PIVOT ...
Citation:

Envoyé par elsuket

ça ne change pas que cela ne devrait pas être fait côté base de données, puisque c'est de la présentation ...

@++ ;)

Faut pas boire tout ce que dit Sql Pro ^^

Pivot et Unpivot sont d'ailleurs très utiles pour normaliser/dé-normaliser.

08/06/2011, 13h21
elsuket

Citation:

Faut pas boire tout ce que dit Sql Pro ^^

Je ne le bois pas, j'en suis convaincu.
C'est de la pure présentation, et j'en veux pour preuve que ce ne sont pas des opérateurs ensemblistes.

Citation:

Pivot et Unpivot sont d'ailleurs très utiles pour normaliser/dé-normaliser.

Pour normaliser, il suffit d'avoir un modèle de données correct.
Pour dénormaliser, il faut commencer par normaliser, puis constater un problème de performance qui ne peut être résolu que par dénormalisation.
Cela réduit déjà la quantité de dénormalisations, puisque si le modèle de données est correct, les optimisations sont simples.
Si néanmoins la dénormalisation est la seule possibilité, les vues indexées répondent très bien à cela.

@++ ;)
08/06/2011, 17h19
Sergejack

Citation:

Envoyé par elsuket

Je ne le bois pas, j'en suis convaincu.
C'est de la pure présentation, et j'en veux pour preuve que ce ne sont pas des opérateurs ensemblistes.

Même employer pour préparer de la présentation l'usage de PIVOT est valable.
Ce qui importe c'est de considérer les échanges de données (entre SQL Server et un serveur web par exemple) et, selon, de faire les traitements à l'endroit le plus approprié (et vous connaissez un endroit fort approprié pour faire des opérations ensemblistes).
Si PIVOT peut apporter une meilleure lisibilité (du code) et/ou des performances : s'en passer serait aussi maladroit que de se passer de l'agrégation.

Citation:

Envoyé par elsuket

Pour normaliser, il suffit d'avoir un modèle de données correct.

Attention, faut pas tomber dans le paradigme du prof.
Ne perdons pas le contact avec le monde réel dans lequel on ne choisit et ne contrôle pas tout. ;)

Citation:

Envoyé par elsuket

Pour dénormaliser, il faut commencer par normaliser, puis constater un problème de performance qui ne peut être résolu que par dénormalisation.
Cela réduit déjà la quantité de dénormalisations, puisque si le modèle de données est correct, les optimisations sont simples.
Si néanmoins la dénormalisation est la seule possibilité, les vues indexées répondent très bien à cela.

@++ ;)

Quel rapport entre vue indexée et emploie ou non de (UN)PIVOT ?
09/06/2011, 09h07
iberserk

Citation:

Si PIVOT peut apporter une meilleure lisibilité (du code) et/ou des performances : s'en passer serait aussi maladroit que de se passer de l'agrégation.

Vous n'aurez pas de meilleures performances avec un PIVOT plutôt que d'effectuer cette 'transformation' sur votre code client.

Citation:

Attention, faut pas tomber dans le paradigme du prof.
Ne perdons pas le contact avec le monde réel dans lequel on ne choisit et ne contrôle pas tout.

Ne perdons pas le contact avec ce vers quoi doit tendre le monde réel, ce n'est pas parce que les circonstances font que l'on n'a pas la main sur tout qu'il faut perdre de vue les améliorations à apporter, je pense que ces remarques ne sont pas constructives dans ce forum surtout pour le public visé...

Citation:

Quel rapport entre vue indexée et emploie ou non de (UN)PIVOT ?

Relisez, il rebondissait sur votre 'dénormalisation' et non sur le PIVOT en particulier.

Citation:

Pour dénormaliser, il faut commencer par normaliser, puis constater un problème de performance qui ne peut être résolu que par dénormalisation.

:ccool:
09/06/2011, 10h20
Sergejack

Citation:

Envoyé par iberserk

Vous n'aurez pas de meilleures performances avec un PIVOT plutôt que d'effectuer cette 'transformation' sur votre code client.

Si et c'est encore plus marquant avec UNPIVOT.
Mais si le client, n'est pas un de vos serveurs (une application sur un poste tiers par exemple) on peut à juste titre être tenté de laissé cette charge de travail au poste tiers afin d'épargner nos serveurs.

Par ailleurs l'écriture une fois connue (de (UN)PIVOT), est plus vite écrite/lue (et donc maintenue) que la plupart de leurs correspondants dans les langages de programmation du/des client(s).

Citation:

Envoyé par iberserk

Ne perdons pas le contact avec ce vers quoi doit tendre le monde réel, ce n'est pas parce que les circonstances font que l'on n'a pas la main sur tout qu'il faut perdre de vue les améliorations à apporter, je pense que ces remarques ne sont pas constructives dans ce forum surtout pour le public visé...

Moi ce que je ne trouve pas constructif, c'est de répéter la pensée des autres sans vérifier tout son bien fondée quand un argument lui est opposé avec insistance.
Par exemple, on m'avait fort tenu tête sur ce forum quand j'expliquais que l'opérateur APPLY avait un potentiel bien plus large que de seulement appeler des fonctions tables... (ça fera peut-être rire ceux qui ne se souviennent pas avoir juré dur comme fer que c'était leur seul emploie).

Il est plus dangereux de se tromper en disant la même chose que tout le monde qu'en disant quelque chose à contre courant.
Surtout quand il est si facile pour tout visiteur confiant de copier-coller l'idée majoritaire vers tels et tels blogs et ainsi consolider l'idée fausse générale.

Citation:

Envoyé par iberserk

Relisez, il rebondissait sur votre 'dénormalisation' et non sur le PIVOT en particulier.

Normaliser/Dénormaliser se font via des mécanismes semblables à ceux ce PIVOT et UNPIVOT.
Toujours est-il que je n'y vois pas l'intérêt d'une vue indexée, et c'est tout ce que je demande.
09/06/2011, 12h27
iberserk

Citation:

Toujours est-il que je n'y vois pas l'intérêt d'une vue indexée

Je crois d'ailleurs que MS a décidé de ne plus les inclure dans la prochaine version de SQL SERVER car personne ne s'en sert:mouarf:

Citation:

Moi ce que je ne trouve pas constructif, c'est de répéter la pensée des autres sans vérifier tout son bien fondée quand un argument lui est opposé avec insistance.

D'accord, donc puisque vous insistez, (gage de véracité apparemment...) fin du débat.
09/06/2011, 13h32
elsuket

Citation:

Envoyé par Sergejack

Même employer pour préparer de la présentation l'usage de PIVOT est valable.

Non puisque justement c'est de la présentation.
Un SGBDR c'est fait pour stocker, manipuler et retrouver des données.
Par pour les présenter.

Citation:

Envoyé par Sergejack

Si PIVOT peut apporter une meilleure lisibilité (du code) et/ou des performances : s'en passer serait aussi maladroit que de se passer de l'agrégation.

Encore une fois l’agrégation est une opération ensembliste, PIVOT et UNPIVOT ne le sont pas !

Citation:

Attention, faut pas tomber dans le paradigme du prof.
Ne perdons pas le contact avec le monde réel dans lequel on ne choisit et ne contrôle pas tout.

Ça c'est vous qui le dites.
Si vous avez envie de faire un modèle de données qui n'est pas normalisé, ça vous regarde.

Citation:

Envoyé par SergeJack

Quel rapport entre vue indexée et emploie ou non de (UN)PIVOT ?

Vous avez écrit :

Citation:

Envoyé par SergeJack

Pivot et Unpivot sont d'ailleurs très utiles pour normaliser/dé-normaliser.

Je ne vois pas en quoi (UN)PIVOT permet de normaliser.
Dénormaliser, OK, mais encore une fois, faire cela à l'aide de (UN)PIVOT n'est pas ensembliste.

Citation:

Envoyé par SergeJack

Par ailleurs l'écriture une fois connue (de (UN)PIVOT), est plus vite écrite/lue (et donc maintenue) que la plupart de leurs correspondants dans les langages de programmation du/des client(s).

C'est bien pour cela que ce n'est pas ensembliste : cette opération se fait sur une liste de valeur connues ... mais qui dit qu'elles le resteront dans la temps ?

Il me semble (je suis un profane en dehors des bases de données) qu'il est bien plus simple de programmer cela du côté client.
Effectuer la recherche de valeurs en T-SQL se ferait avec du code T-SQL dynamique, qui, s'il est pratique, est bien moins facilement maintenable que du pur code T-SQL.

Citation:

Envoyé par SergeJack

Moi ce que je ne trouve pas constructif, c'est de répéter la pensée des autres sans vérifier tout son bien fondée quand un argument lui est opposé avec insistance.

Écrire cela c'est mépriser ceux qui participent à ce forum.
Je suppose qu'on a tous l'habitude de tester nos codes et l'on tombe parfois sur des cas particuliers.
Ce sont ces cas particuliers que vous exposez comme un cas général de façon régulière ici.

Citation:

Envoyé par SergeJack

Toujours est-il que je n'y vois pas l'intérêt d'une vue indexée, et c'est tout ce que je demande.

La conclusion est là ... :D

Citation:

Envoyé par iberserk

Je crois d'ailleurs que MS a décidé de ne plus les inclure dans la prochaine version de SQL SERVER car personne ne s'en sert :mouarf:

Ben moi si, et ça cartonne ;)

@++ ;)
09/06/2011, 14h22
iberserk

Citation:

Il me semble (je suis un profane en dehors des bases de données) qu'il est bien plus simple de programmer cela du côté client.

Moi je connais bien les deux mondes et je suis PRO SQL pourtant je préfère demander à mes développeurs d'alimenter un objet structuré avec une requête dépourvue du PIVOT surtout quand cette requête est massivement exécutée...
C'est totalement trivial et surtout... complètement dynamique et compilé...

Personnellement je ne me suis servi de UNPIVOT que pour un batch SSIS dont les fichiers plats en entrée était dynamique...