Requete pivot et totaux

**gudul** · 19/11/2013, 00h53

Bonjour,

Je suis sur SQLServeur 2008.

J’ai une vue avec CODE_ARTICLE, CODE_CLIENT, DATE_CMD, QTE_CMD
Je voudrais construire un tableau croisé dynamique avec des dates regroupées en mois ou semaines en colonnes + un total au bout et en ligne la somme des quantités par CLIENT et TOTALE, Filtré sur le Code article.
Le dernier mois étant le mois en cours et remonté sur 12 MOIS

Code :

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

1
2
3
4
5
6
ARTICLE	|	CLIENT	|Décembre 2012	|Janvier 2013	|Février 2013	|Mars 2013	|Avril 2013	|Mai 2013	|Juin 2013	|Juillet 2013	|Août 2013	|Septembre 2013	|Octobre 2013	|Novembre 2013	|	Total
BANANE	|	TOTO	|	0	|	0	|	0	|	120	|	240	|	90	|	40	|	0	|	0	|	100	|	120	|	230
BANANE	|	BEBERT	|	0	|	0	|	20	|	40	|	300	|	120	|	140	|	120	|	70	|	100	|	140	|	260
BANANE	|	FRUTI	|	20	|	30	|	60	|	90	|	140	|	60	|	140	|	120	|	320	|	200	|	180	|	130
BANANE	|	TITI	|	0	|	0	|	0	|	10	|	100	|	40	|	40	|	50	|	70	|	80	|	100	|	200
TOTAL	|		|	20	|	30	|	80	|	250	|	780	|	310	|	360	|	290	|	460	|	480	|	540	|	820

Merci de vos propositions

**aieeeuuuuu** · 19/11/2013, 12h34

Bonjour,

Le Problème avec une telle présentation, outre que l'opération de pivot n'est pas le fort des SGBDR, c'est surtout que vos nom de colonne changeront tous les mois !
Il serait plus simple et plus performant de récupérer les données en colonne, et des les présenter ensuite comme vous le voulez en utilisant le langage du programme appelant.

Vous pouvez obtenir toutes les informations pour votre table avec une requête comme celle-ci :

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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
 
 
;WITH Nb AS(
	SELECT 0 as n
	UNION ALL
	SELECT n - 1
	FROM Nb
	WHERE n > -11
)
, Mois AS (--génération des mois
	SELECT 
		DATEADD(
			MONTH
			,DATEDIFF(
				MONTH
				,0
				,GETDATE()
			) + n
			,0
		) AS Debut
		,
		DATEADD(
			MONTH
			,DATEDIFF(
				MONTH
				,0
				,GETDATE()
			) + n + 1
			,0
		) AS Fin
	FROM Nb
),
Client AS (--a remplacer par la table des clients
	SELECT DISTINCT CODE_CLIENT FROM LaVue
	)
,Article AS (--a remplacer par la table article
	SELECT DISTINCT CODE_ARTICLE FROM LaVue
)
SELECT 
	Debut
	,Article.CODE_ARTICLE
	,Client.CODE_CLIENT 
	,COALESCE(SUM(QTE_CMD), 0) AS Total
FROM Mois
CROSS JOIN Client
CROSS JOIN Article
LEFT JOIN LaVue
	ON	LaVue.DATE_CMD >= Mois.Debut
	AND	LaVue.DATE_CMD < Mois.Fin
	AND 	LaVue.CODE_ARTICLE = Article.CODE_ARTICLE
	AND	LaVue.CODE_CLIENT = Client.CODE_CLIENT
GROUP BY CUBE (
		Debut,
		(Article.CODE_ARTICLE	,Client.CODE_CLIENT)
		)
ORDER BY 
	Debut
	,Article.CODE_ARTICLE
	,Client.CODE_CLIENT

Notez que si l'on ne s'appuie que sur la vue, il pourra manquer des données si

1/ il n'y a pas eu de commande un mois donné :
==> Je génère une table des 12 derniers mois, mais il serait préférable de passer par une vrai table calendrier
2/ si un client n'a pas commandé un mois donné, ou si un article n'a pas été commandé un mois donné :
==> Je génère des pseudo table Article et Client à partir de votre vue, mais il serait mieux d'utiliser vos table Article et Client...

**Lyche** · 20/11/2013, 15h08

Un PIVOT

http://technet.microsoft.com/fr-fr/l...=sql.105).aspx

N'aurait pas été plus simple pour réaliser ce processus?

Cordialement,
Lyche

**gudul** · 20/11/2013, 18h11

Il est claire que le pivot serait interessant. La première difficulté est l'ordre et le nomage de colonne mois/Année qui dépend du mois en cours.

**Lyche** · 20/11/2013, 18h18

Envoyé par gudul

Il est claire que le pivot serait interessant. La première difficulté est l'ordre et le nomage de colonne mois/Année qui dépend du mois en cours.

après, il y a peut-être moyens de les nommer de façon "dynamique" j'avais réfléchis à une requête de pivot qui se construisait en fonction du nombre d'occurence à mettre dans le pivot (valeurs distinctes dans les colonnes servant de pivot).

Je n'ai plus le code sous la main puisqu'il est dans une ancienne entreprise, je peux regarder pour y réfléchir un peu.

En tout cas, c'est une piste

**aieeeuuuuu** · 20/11/2013, 18h56

Ce qui complique la requete que j'ai proposée est de "boucher les trous" pour les données manquantes.

Sans cela, elle se résume à ça :

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
19
20
21
22
23
 
SELECT 
	CODE_ARTICLE
	,CODE_CLIENT 
	,DATEDIFF(MONTH, GETDATE(), DATE_CMD) as diff
	,COALESCE(SUM(QTE_CMD), 0) AS Total
FROM LaVue
WHERE DATE_CMD > DATEADD(
							MONTH
						,	DATEDIFF(
								MONTH
								,0
								,GETDATE()
							)-11
						,	0
					)					
GROUP BY CUBE (
		DATEDIFF(MONTH, GETDATE(), DATE_CMD) ,
		(CODE_ARTICLE	,CODE_CLIENT)
		)
 
ORDER BY 
	CODE_ARTICLE	,CODE_CLIENT, DATEDIFF(MONTH, GETDATE(), DATE_CMD)

Cependant il est en effet possible de faire une requete PIVOT, mais ça ne résoudra le problème des données manquantes que pour les mois.
Par ailleurs, on est obligé de mettre des noms de colonne fixes, donc si la requete n'est pas accompagnée de sa date d'éxécution, les données ne valent plus rien.
quant au fait que ce soit plus simple... chacun son gout, car ça donne ça :

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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
 
WITH CTE AS (
	SELECT 
		CODE_ARTICLE
		,CODE_CLIENT
		,DATEDIFF(MONTH, GETDATE(), DATE_CMD) as diff
		,QTE_CMD
	FROM LaVue
	WHERE DATE_CMD > DATEADD(
							MONTH
						,	DATEDIFF(
								MONTH
								,0
								,GETDATE()
							)-11
						,	0
					)					
),
Result AS (
	SELECT 
				CODE_ARTICLE
			,	CODE_CLIENT
			,	COALESCE([-11], 0) AS MoisMoins11
			,	COALESCE([-10], 0) AS MoisMoins10
			,	COALESCE([-9], 0) AS MoisMoins9
			,	COALESCE([-8], 0) AS MoisMoins8
			,	COALESCE([-7], 0) AS MoisMoins7
			,	COALESCE([-6], 0) AS MoisMoins6
			,	COALESCE([-5], 0) AS MoisMoins5
			,	COALESCE([-4], 0) AS MoisMoins4
			,	COALESCE([-3], 0) AS MoisMoins3
			,	COALESCE([-2], 0) AS MoisMoins2
			,	COALESCE([-1], 0) AS MoisMoins1
			,	COALESCE([0], 0)  AS MoisCourant
			,	
				COALESCE([-11], 0)
				+COALESCE([-10], 0)
				+COALESCE([-9], 0)
				+COALESCE([-8], 0)
				+COALESCE([-7], 0)
				+COALESCE([-6], 0)
				+COALESCE([-5], 0)
				+COALESCE([-4], 0)
				+COALESCE([-3], 0)
				+COALESCE([-2], 0)
				+COALESCE([-1], 0)
				+COALESCE([0], 0) 
					 AS Total
	FROM cte
	PIVOT (
		SUM(QTE_CMD)
		FOR diff IN ([0], [-1], [-2], [-3], [-4], [-5], [-6], [-7], [-8], [-9], [-10], [-11])
	) AS P
)
SELECT 
			CODE_ARTICLE
		,	CODE_CLIENT
		,	MoisMoins11
		,	MoisMoins10
		,	MoisMoins9
		,	MoisMoins8
		,	MoisMoins7
		,	MoisMoins6
		,	MoisMoins5
		,	MoisMoins4
		,	MoisMoins3
		,	MoisMoins2
		,	MoisMoins1
		,	MoisCourant
		,	Total
FROM Result
UNION ALL
SELECT 
		'Total'
	,	'' 
	,	SUM(MoisMoins11)
	,	SUM(MoisMoins10)
	,	SUM(MoisMoins9)
	,	SUM(MoisMoins8)
	,	SUM(MoisMoins7)
	,	SUM(MoisMoins6)
	,	SUM(MoisMoins5)
	,	SUM(MoisMoins4)
	,	SUM(MoisMoins3)
	,	SUM(MoisMoins2)
	,	SUM(MoisMoins1)
	,	SUM(MoisCourant)
	,	SUM(Total)
FROM Result

Par contre en effet, on obtient ça :

Code :

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

1
2
3
4
5
6
7
8
 
CODE_ARTICLE CODE_CLIENT MoisMoins11 MoisMoins10 MoisMoins9  MoisMoins8  MoisMoins7  MoisMoins6  MoisMoins5  MoisMoins4  MoisMoins3  MoisMoins2  MoisMoins1  MoisCourant Total
------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A            titi        0           262144      524288      0           0           0           0           0           0           0           0           0           786432
B            titi        0           0           4194304     8388608     0           0           0           0           0           0           0           0           12582912
A            toto        0           196608      0           0           0           0           0           0           0           0           0           0           196608
B            toto        0           0           3145728     0           0           0           0           0           0           0           0           0           3145728
Total                    0           458752      7864320     8388608     0           0           0           0           0           0           0           0           16711680

soit exactement la présentation que gudul souhaitait (au nom des colonnes près...).
Mais a mon avis au prix de performances en baisse...

**gudul** · 21/11/2013, 11h16

Cette dernière me convient bien. Elle correspond à ce que je recherche. Je filtre en plus sur l'article. Dès que possible, je teste avec mes colonnes et vous ferez part de mes remarques

Requete pivot et totaux

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager