Simple Crosstab Procedure with Power

CREATE procedure [dbo].[UberCrossTab]
 @pivotRowFields as nvarchar(1000),
 @pivotField as nvarchar(100),
 @pivotTable as nvarchar(1000),
 @aggField as nvarchar(100),
 @aggFunc as nvarchar(10),
 @pivotWhere as nvarchar(1000) = Null,
 @pivotOrder as nvarchar(100) = Null,
 @colField as nvarchar(100) = Null,
 @colTable as nvarchar(100) = Null,
 @colWhere as nvarchar(300) = Null,
 @colTop as nvarchar(10) = Null,
@pivotToTable as nvarchar(30) = Null
AS 
/*
----------------------------------------------------------------------
UberCrossTab for SQL Server 2005
----------------------------------------------------------------------
Created by Chris Gruel (cgruel at g mail dot com)
3/23/2009
With help from article by András Belokosztolszki at
http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx
----------------------------------------------------------------------
The declarations below have been retained for explanation of 
UberCrossTab's variables and for easy debugging if needed.
----------------------------------------------------------------------
--*/
/*
--This is the field name of where your dynamic columns will come from 
--If left null then the pivot table data will be used
--Use this only if you want to use a table that is different than the pivot table/view you are getting your data from for column names
--(In Access this is called the Column Heading on a Crosstab Query)
declare @colField as nvarchar(100)

--This is the table name of the table you want to pull your column names from
--Use this only if you want to use a table that is different than the pivot table/view you are getting your data from for column names
declare @colTable as nvarchar(100)

--Use this only if you want to use filter from the optional table that is different than the pivot table/view you are getting your data from
declare @colWhere as nvarchar(300)

--Use this to limit the number of columns you are pulling your column names from 
--Use this only if you want to use a table that is different than the pivot table/view you are getting your data from for column names
declare @colTop as nvarchar(10)

/* This is the section of the pivot data */--This is the comma seperated list of field(s) from the pivot table/view to include with the data 
--(In Access these are called the Row Heading on a Crosstab Query)
-- eg. 'column1,column2,column3' or 'column1'
declare @pivotRowFields as nvarchar(1000)

--This is the field in the pivot table/view that matches the 
declare @pivotField as nvarchar(100)

--This is the table/view for the pivot
--This can also be an entire query wrapped in a parenthesis
declare @pivotTable as nvarchar(1000)

--This is the optional WHERE clause for the pivot table/view
declare @pivotWhere as nvarchar(300)

--This is the optional ORDER BY statement for the pivot table/view
declare @pivotOrder as nvarchar(100)

--This is the field in the pivot table/view that will be aggregated
--(In Access this is called the Value Field on a Crosstab Query)
declare @aggField as nvarchar(100)

--This is the function to perform on the aggregate field
-- eg. SUM or MAX or MIN or AVG or any valid aggregate function
declare @aggFunc as nvarchar(10)

--This is used to place all pivot data into a table. If the table exists, it will be dropped.
--Otherwise the pivot data will go straight to the table and the procedure will use a select
--statement to return the data using that table
declare @pivotToTable nvarchar(30)
--*/
/* Basic Error Handler - work in progress */If len(@pivotOrder) > len(@pivotRowFields)
 RAISERROR ('UberCrossTab Error: You cannot include rows in the ORDER BY statement that are not in the @pivotRowFields variable.',10,1) WITH NOWAIT

--This is the variable that stores all the column names
declare @cols as nvarchar(max)

If object_id('tempdb..#cols') is not null
 drop table #cols

create table #cols (Col nvarchar(max))

/* This is example data
set @colField = 'fw'
set @colTable = 'fscl_yr_wk'
set @colWhere = 'fw between (select min(fw) from CC_MKDN_DLRS) AND (SELECT max(fw) from CC_MKDN_DLRS)'
set @colTop = '17'

set @pivotRowFields = 'SubDpt'
set @pivotField = 'fw'
set @pivotTable = 'CC_MKDN_DLRS'
set @aggField = '[$MKDN]'
set @aggFunc = 'SUM'
set @pivotWhere = 'subdpt = ''24'''
set @pivotOrder = 'SubDpt'
--*/If @pivotToTable is not null
begin
 declare @query3 as nvarchar(1000)
 set @query3 = 'If object_id(''' + @pivotToTable + ''') is not null
 begin
 PRINT ''Table to post data to exists, dropping table.''
 drop table ' + @pivotToTable + '
 end'
 exec (@query3)
end

set @colField = case when @colField is null then @pivotField else @colField end
set @colTable = case when @colTable is null then @pivotTable else @colTable end
set @colWhere = case when @colField is null and @pivotWhere is not null then @pivotWhere else @colWhere end

DECLARE @query NVARCHAR(max)
set @query = '
DECLARE @cols NVARCHAR(max)
SELECT @cols = STUFF(
( SELECT DISTINCT ' + case when @colTop is not null then 'Top ' + @colTop else '' end + '''],['' + t2.' + @pivotField + '
FROM
(SELECT CONVERT(nvarchar(max),a.' + @colField + ') AS ' + @pivotField + '
FROM
(SELECT DISTINCT ' + @colField + '
FROM ' + @colTable + '
 ' + case when @colWhere is not null then 'WHERE ' + @colWhere else '' end + '
) AS a
) AS t2
ORDER BY ''],['' + t2.' + @pivotField + ' FOR XML PATH('''')
), 1, 2, '''') + '']''
select @cols
'
insert into #cols
exec (@query)

set @cols = (select * from #cols)

drop table #cols

DECLARE @query2 NVARCHAR(max)
SET @query2 = N'SELECT ' + @pivotRowFields + ', '+
@cols + '
' + case when @pivotToTable is not null then 'INTO ' + @pivotToTable else '' end + '
FROM
(SELECT ' + @pivotRowFields + '
,' + @pivotField + '
,' + @aggField + '
FROM ' + @pivotTable + ' as a
' + case when @pivotWhere is not null then 'WHERE ' + @pivotWhere else '' end + ') p
PIVOT
(
' + @aggFunc + '(' + @aggField + ')
FOR ' + @pivotField + ' IN
( '+
@cols +' )
) AS pvt
' + case when @pivotOrder is not null then 'Order by ' + @pivotOrder else '' end + ';'

print 'Pivot Query used...'
print @query2

If @pivotToTable is not null
begin
 print 'Executing insert statement into a table.'
 exec (@query2)
 declare @query4 as nvarchar(1000)
 set @query4 = 'SELECT * FROM ' + @pivotToTable
 print 'New table [' + @pivottoTable + '] created.'
 exec (@query4)
end
else
begin
 print 'Presenting data without saving.'
 exec (@query2)
end