Discussion :

[Sergejack]

Bonjour,

j'ai bcp de mal à comprendre l'utilisation de PIVOT et UNPIVOT.
Est-ce que par exemple vous pourriez me dire comment avec ces opérateurs je peux obtenir une table (résultat de requête) dont les colonnes seraient 1, 2, ..., 9 avec une ligne où il n'y aurait que des valeurs 'A' pour chaque colonne et une ligne où il n'y aurait que des valeurs 'B' ?

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
 
; WITH AB (
 Lettre
)
AS
(
 SELECT 'A'
 UNION
 SELECT 'B'
)
, 1A9 (
 Chiffre
)
AS
(
 SELECT 1
 UNION
 SELECT 2
 UNION
 SELECT 3
 UNION
 SELECT 4
 UNION
 SELECT 5
 UNION
 SELECT 6
 UNION
 SELECT 7
 UNION
 SELECT 8
 UNION
 SELECT 9
)
 
???

1|2|3|4|5|6|7|8|9
A|A|A|A|A|A|A|A|A
B|B|B|B|B|B|B|B|B

Merci

[elsuket]

Bonjour,

L'utilisation des opérateurs PIVOT et UNPIVOT requiert l'utilisation d'une fonction d'aggrégation. Les colonnes proviennent des valeurs du SELECT.
Un bon exemple par Pinal Dave ici.

Je suppose que vous l'avez lu, mais je le répète : ces deux opérateurs ne sont pas ensemblistes de par le simple fait qu'ils gèrent de la présentation de données, ce qui est normalement à déplacer du côté de l'application cliente de la base de données

@++

[Waldar]

Je suppose que vous l'avez lu, mais je le répète : ces deux opérateurs ne sont pas ensemblistes de par le simple fait qu'ils gèrent de la présentation de données, ce qui est normalement à déplacer du côté de l'application cliente de la base de données

Mais non mais non, si vous avez l'œil vous aurez reconnu :
http://www.developpez.net/forums/d82...hallenge-15-a/

[elsuket]

Oui , mais l'introduction est pourtant claire :

This challenge is derived from a realistic reporting scenario that requires the transformation of rows into columns. This is commonly known as a matrix, pivot, or crosstab query

Ce qui est en gras dans la citation peut tout à fait être traité par une application SSRS, ou par Excel, qui sont conçus pour cela

@++

[Sergejack]

Mais non mais non, si vous avez l'œil vous aurez reconnu :
http://www.developpez.net/forums/d82...hallenge-15-a/

Pas exctement, c'est bien parce les tsql challenge que mon intention a été retenue sur PIVOT/UNPIVOT depuis la solution au challenge 11 (11, je crois).

Mais, c'est pour une requête à usage personnel que j'aimerais comprendre mieux PIVOT et UNPIVOT.

[mikedavem]

Oui , mais l'introduction est pourtant claire :

Ce qui est en gras dans la citation peut tout à fait être traité par une application SSRS, ou par Excel, qui sont conçus pour cela

@++

Effectivement tu as raison, je pense qu'ici il faut savoir que cela existe même si cela reste de la cosmétique ... et en terme de performance ce n'est pas ce qu'il y a de mieux .. Je crois qu'on est toujours d'accord hein

Cependant il arrive que même sur Excel, le client final veuille disposer de ses données sous la forme finale , sans avoir à faire un tableau croisé dynamique (si si j'ai vu ) donc l'utilisation de l'opérateur PIVOT ou SELECT avec des CASES devient obligatoire ... Bien sûr il faut faire le choix entre comodité et performance dans ce cas là.

>> Sergejack : Cela me paraît difficile de faire ce que vous voulez ... l'opérateur PIVOT aggrège les données (COUNT, AVG, MAX etc ...) comme pour un tableau dynamique sur Excel. La requête que vous voulez sortir ne se base pas sur ce principe ...

++

[Sergejack]

J'ai trouvé comment faire.

[elsuket]

Effectivement tu as raison, je pense qu'ici il faut savoir que cela existe même si cela reste de la cosmétique ... et en terme de performance ce n'est pas ce qu'il y a de mieux .. Je crois qu'on est toujours d'accord hein

Cependant il arrive que même sur Excel, le client final veuille disposer de ses données sous la forme finale , sans avoir à faire un tableau croisé dynamique (si si j'ai vu ) donc l'utilisation de l'opérateur PIVOT ou SELECT avec des CASES devient obligatoire ... Bien sûr il faut faire le choix entre comodité et performance dans ce cas là.

Effectivement nous sommes toujours d'accord .

Mais, pour avoir fait un peu de VBA Excel, je reste persuadé que l'on peut écrire une macro qui, s'exécutant à l'ouverture du fichier, pourrait effectuer ce genre de traitements

@++

[mikedavem]

J'ai trouvé comment faire.

Et cela donne ?

++

[Sergejack]

Et cela donne ?

++

Je n'ai pas conservé le code, mais c'est pas sorcier.

- isolation de la table source du pivot en mettant les deux dans un sous query
- utilisation de query dynamiques
- pour obtenir notre liste de colonne : utilisation de la clause "FOR XML PATH('')" afin de concaténner des lignes de table en une seule chaîne ou (et les puristes préfereront peut-être) utilisation de CTE récursive pour faire la concaténation.

EDIT : ha ben, j'ai encore les bases du code finallement :

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
 
DECLARE @Columns VARCHAR(100)
 
; WITH A19 (
 Chiffre
)
AS
(
 SELECT 10
 UNION
 SELECT 21
 UNION
 SELECT 35
 UNION
 SELECT 47
 UNION
 SELECT 59
 UNION
 SELECT 61
 UNION
 SELECT 72
 UNION
 SELECT 80
 UNION
 SELECT 93
)
, SConcat
(
	S
)
AS
(
	SELECT
		STUFF((
			SELECT 
				', [' + CAST(Chiffre AS VARCHAR) + ']'
			FROM A19
			FOR XML PATH('')
		) , 1, 2, '')
 
)
 
SELECT S
FROM SConcat
/*
SELECT 
	P.*
FROM AB
CROSS APPLY (
	SELECT *
	FROM A19
	PIVOT (MIN(Lettre) FOR Chiffre IN ([1], [2])) AS P
) AS P
 
AB (
 Lettre
)
AS
(
 SELECT 'A'
 UNION
 SELECT 'B'
)
*/