Discussion :

[Fabby69]

Bonjour à tous,

Combien d'entre vous ont pesté contre SQL Server parcequ'on ne peut pas y créer simplement des requêtes croisée ?? En tout cas moi j'en fait partie . En apprenant que cette fonctionnalité apparaitrait enfin dans SQL Server 2005, je me disais que j'allais enfin pouvoir faire tout ce que je veux, mais apparemment , c'est une solution mangeuse de ressource rapportée d'Access.

En cherchant sur le net j'ai enfin trouvé mon bonheur pour créer des requêtes croisées sous SQL Server 2K et en faire ce que je veux, alors dans mon élan de joie, je tiens à vous faire partager cette découverte .

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
 
CREATE PROCEDURE spPivot_Table 
@cTable varchar(80),
@cDown varchar(80),
@cAcross varchar(80),
@cFunc varchar(80),
@cAggFld varchar(80),
@cWhere varchar(200)
As
 
if exists(select name from sysobjects where type='U' and name = 'TempUniq') 
Drop Table dbo.TempUniq
 
if exists(select name from sysobjects where type='U' and name = 'TempPivot') 
Drop Table dbo.TempPivot
 
Declare @cColTtl varchar(80),
@cSQLStr varchar(200),
@cSQL varchar(8000),
@nRows Int,
@nCntr Int
 
-- Generate Pivot Key Table
Set @cSQL = 'Select Distinct '+@cAcross+' as Pivot_Value Into TempUniq From '+@cTable+' Where '+@cWHere+' Order By 1 '
Exec(@cSQL)
Select IDENTITY(int, 1,1) as Pivot_Row,@cFunc as Pivot_Func, @cAggFld as Pivot_AggFld,@cAcross as Pivot_Fld,@cColTtl as Pivot_Col,Pivot_Value,@cSQLStr as Pivot_SQL Into TempPivot From TempUniq Order By Pivot_Value
 
-- Generate Pivot_Col !! Need a Better T -- echnique for Safe Characters !!
Update TempPivot Set Pivot_Col = 'Col_'+Replace(RTrim(LTrim(Convert(varchar(80),Pivot_Value))),' ','_')
 
-- Build and Execute Pivot SQL
Update TempPivot Set Pivot_SQL=LTrim(RTrim(Pivot_Col))+'='+Pivot_Func+'(case when '+Pivot_Fld+'=Pivot_Value and Pivot_Col='''+Pivot_Col+''' Then '+Pivot_AggFld+' else Null end)'
Select @nRows=Max(Pivot_Row),@nCntr=Min(Pivot_Row) From TempPivot
Set @cSQL=''
While @nCntr <= @nRows
Begin
   Select @cSQL=@cSQL+','+Pivot_SQL From TempPivot Where Pivot_Row=@nCntr
   Set @nCntr=@nCntr+1
End
 
Set @cSQL='Select '+@cDown+','+Substring(@cSQL,2,8000)+' From '+@cTable+' Join TempPivot on('+@cAcross+'=Pivot_Value) Where '+@cWhere+' Group By '+@cDown+' Order By '+@cDown
Exec(@cSQL)
 
 
 
-- To Do
-- Add Logic for Col Names ie remove Spa -- ces, No Numerics, Nulls...
-- Add some error trapping
-- Add some logic for Destination 
 
-- Capabilities
-- Any Combination to "Down" or By Field -- s
-- Functions Available: Sum, Avg, Min, M -- ax, Count, STD, ...
-- Across may be an expression Substring -- (ShipCountry,1,1) = Across A,B,C,D,...-- r> 
-- Samples
-- Exec spPivot_Table {Table},{By Fields -- },{Across Colums},{Agg Function},{Pivot -- Field},{Filer}
-- Exec spPivot_Table 'Orders','ShipCoun -- try','Year(OrderDate)','Sum','Freight',' -- 1=1'
-- Exec spPivot_Table 'Orders','ShipCoun -- try','Year(OrderDate)','Sum','Freight',' -- Year(OrderDate)>1996'
-- Exec spPivot_Table 'Orders','Employee -- ID,ShipCountry','Year(OrderDate)','Sum', -- 'Freight','1=1'
-- Exec spPivot_Table 'Orders','Employee -- ID,ShipCountry','Year(OrderDate)','Sum', -- 'Freight','1=1'
-- Exec spPivot_Table 'Orders','Employee -- ID,ShipCountry','Substring(ShipCountry,1 -- ,1)','Sum','Freight','1=1'

Bon Courage

[Jean-Michel]

J'adore les choses simples. Ce qui n'est apparemment pas le cas ici.
Toujours est-il, je n'en connaissais pas la procédure.
Merci.

[SQLpro]

C'est vraiment genre de connerie qui n'ont rien à faire au sein du SGBDR car cela peut être fait au niveau client...

Voici ce que j'écrit sur MS SQL Server 2005 à ce sujet :

1.8 Opérateur PIVOT / UNPIVOT

L'horrible, mais traditionnel opérateur PIVOT, bien qu'absent de la norme SQL fait son apparition dans SQL Server 2005 afin de permettre de réaliser des tableaux croisés. Il s'agit simplement d'une astuce cosmétique pour présenter des données avec de nouvelles colonnes dont les noms sont extraits d'une des colonnes de la requête.

Exemple :

[...]

ATTENTION : Si cette technique permet de réconcilier les afficionados d'Access et des tableaux croisés avec SQL Server, tant mieux. Mais ce "truc" possède de très nombreux inconvénients majeurs :

1) aucune conformité aux normes SQL et franchement pas standard : la transformation de valeurs en nom de colonnes est une absurdité et le recours aux crochets pour nommer les colonnes provenant des valeurs n'obéit à aucune logique, ni aucun standard si ce n'est les spécificités de MS SQL Server.

2) même si le plan de requête parait plus simple, il n'est pas certain que l'effort en terme d'E/S soit moindre. En effet l'utilisation d'un tel opérateur empêche en principe l'utilisation des index.

3) toute opération cosmétique grève énormement les performances d'un serveur SQL parce qu'il n'a pas été prévu pour cela, alors qu'un outil de présentation spécialisement conçu à cet effet pourra donner le même rendu en minimisant les ressources.

4) la clause IN de l'opérateur PIVOT n'a pas été rendue générique. En l'occurrence l'écriture très tentant d'une requête du genre :
PIVOT (SUM(VTE_CA) FOR VTE_PERIODE IN (VTE_PERIODE) AS TRIMESTRE
ou encore :
PIVOT (SUM(VTE_CA) FOR VTE_PERIODE IN (SELECT DISTINCT VTE_PERIODE FROM T_VENTE_VTE) AS TRIMESTRE
n'est pas possible. Il faut spécifier "en dur" et connaître d'avance les colonnes !

En conclusion : les opérateurs PIVOT / UNPIVOT revêtent peu d'intérêtn et sont à éviter systématiquement pour qui veut des performances.

A lire : http://sqlpro.developpez.com/SQL_Server_2K5/N1.php

A +