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' |
