Discussion :

[minijim]

Bonjour,

je ne pense pas que mon problème soit bien compliqué, mais je n'ai pour l'instant pas pu y trouver de réponse sur internet :

Je dispose d'une table du style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
PERIODE     NOM      VALEUR
   1         a         15
   1         b         10
   1         c         60
   3         a        -40
   5         c         42

que je voudrais faire pivoter pour obtenir la table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
PERIODE      a          b      total : a+b+c
   1        15         10            85
   3       -40        NULL          -40
   5       NULL       NULL           42

Mon souci est que je n'arrive pas à obtenir la dernière colonne.
Ma requète initiale est la suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
SELECT [PERIODE], sum([a]) as [a], sum([b]) as [b]
INTO table2
FROM  (SELECT PERIODE,NOM,VALEUR FROM table1) as sourcetable
PIVOT (sum([VALEUR]) for [NOM] in (a,b)) as pivottable
GROUP BY PERIODE
ORDER BY PERIODE;

Cependant si je la transforme en

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
SELECT [PERIODE], sum([a]) as [a], sum([b]) as [b], sum([VALEUR]) as TOTAL
INTO table2
FROM  (SELECT PERIODE,NOM,VALEUR FROM table1) as sourcetable
PIVOT (sum([VALEUR]) for [NOM] in (a,b)) as pivottable
GROUP BY PERIODE
ORDER BY PERIODE;

J'ai alors une erreur " Invalid column name 'valeur' ".

Merci d'avance pour toute suggestion

[al1_24]

PIVOT n'est pas une clause du langage SQL normalisé et est spécifique à certains SGBD.
Lequel utilises-tu ?

[minijim]

ah oui, pardon : j'utilise SQL Server 2008.

[al1_24]

Il y a une manière simple de résoudre ton problème avec du code SQL tout ce qu'il y a de plus générique :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT  periode
    ,   SUM(CASE nom WHEN 'a' THEN valeur END)  AS a
    ,   SUM(CASE nom WHEN 'b' THEN valeur END)  AS b
    ,   SUM(valeur) AS total
FROM    sourcetable
GROUP BY periode

[minijim]

Merci pour ton aide Alain.
Je crois que je vais utiliser la méthode que tu me proposes, qui était celle que je connaissais avant la méthode pivot proposée depuis SQL Server 2005.

En fait je trouvais que cette méthode reste assez peu "agréable" à utiliser (en terme de longueur de code), notamment pour des tables qui contiennent beaucoup de noms (a,b,...) différents, et qui obligent donc, je pense, soit à répéter autant de fois qu'il faut l'instruction "sum case when then end", soit à utiliser une boucle.
(d'après ce que j'ai vu sur un tutorial http://fadace.developpez.com/mssql/pivot/)

Du coup en utilisant la deuxième solution, j'ai l'impression de pas pouvoir faire beaucoup plus court que le code ci-dessous :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
declare @cpt as varchar(2),@sql varchar(5000),@var varchar(5000)
declare c cursor for select distinct nom from table1 where nom in ('a','b',[...])
open c
fetch c into @cpt
set @sql=''
while @@FETCH_STATUS = 0
	begin
	select @sql=@sql+'SUM( case nom when '+char(39)+cast(@cpt as varchar(2))+char(39)+' then valeur end) as ['+ cast(@cpt as varchar(2)) +'],'
	fetch c into @cpt
	end
close c
deallocate  c
set @var=left(@sql,len(@sql)-1)
exec('SELECT periode,sum(valeur) as total,'+ @var +' FROM table1 GROUP BY periode;');

Bref... dommage que pivot ne soit pas une clause du langage SQL normalisé

[al1_24]

L'une des raisons pour lesquelles PIVOT a peu de chances d'entrer dans le standard des commandes SQL est que le nombre de colonnes retourné par une telle requête est imprévisible ; il dépend du contenu de la table consultée.
Lorsque la requête est exécutée à l'intérieur d'une application, l'échange de données entre SQL et langage de programmation s'effectue le plus souvent par un tableau, dont le nombre de colonnes est fixé.
D'où l'incompatibilité de PIVOT et de la norme...

[Waldar]

Non non, l'opérateur PIVOT nécessite aussi l'énumération de toutes les colonnes pivotées.
C'est juste une autre syntaxe plus concise.

Pour le problème du départ, il y a beaucoup de choses inutiles dans votre requête, vous pouvez synthétiser ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
With Table1 (PERIODE, NOM, VALEUR) as
(
select 1, 'a',  15 union all
select 1, 'b',  10 union all
select 1, 'c',  60 union all
select 3, 'a', -40 union all
select 5, 'c',  42
)
SELECT PERIODE, a, b,
       coalesce(a, 0) + coalesce(b, 0) + coalesce(c, 0) as Total
  FROM table1
 PIVOT (sum([VALEUR]) FOR [NOM] IN (a, b, c)) AS pvt;
 
PERIODE     a           b           Total
----------- ----------- ----------- -----------
1           15          10          85
3           -40         NULL        -40
5           NULL        NULL        42

L'astuce pour votre besoin étant de pivoter aussi sur votre valeur C mais de ne pas la sélectionner.

[minijim]

Merci Waldar et Alain pour ces explications