CREATE PROCEDURE spPivot_Table
@cTable varchar(80),
@cDown varchar(80),
@cAcross varchar(80),
@cFuncvarchar(80),
@cAggFld varchar(80),
@cWhere varchar(200)
As
Drop Table TempUniq
Drop Table 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'
|
