Instant and Dynamic Cross-Tab Reports using PIVOT in SQL Server 2005

It is a usual practice that we have data in a SQL Server table but without using any frontend application tool or programming language we find quite difficult to produce the data in our desired format. Most of the times the format is a 2 dimensional matrix sort and the report generation comes out to be quite cumbersome. I would not waste enough time in elaborating but would come straight to the point. I would explain with an example and give you all a Stored Procedure which would accept the input table and field on which the dynamic header should appear and immediately generate the output table.
Here we go,
 1. Create a table as follows,
     Create table DistrictData(DistName nvarchar(100) not null,MonYear varchar(6) not null,Expense float)
 2. Insert data into the table,
insert into DistrictData values('Agra','012011',2300.00)
insert into DistrictData values('Agra','022011',2600.00)
insert into DistrictData values('Agra','032011',1100.00)
insert into DistrictData values('Agra','042011',5500.00)
insert into DistrictData values('Agra','052011',1600.00)
insert into DistrictData values('Agra','062011',3400.00)
insert into DistrictData values('Aligarh','012011',1800.00)
insert into DistrictData values('Aligarh','022011',1100.00)
insert into DistrictData values('Aligarh','032011',1700.00)
insert into DistrictData values('Aligarh','042011',2400.00)
insert into DistrictData values('Aligarh','052011',2900.00)
insert into DistrictData values('Aligarh','062011',2700.00)

3. The data in the table can be seen by giving
(select * from DistrictData),

DistName	MonYear	Expense
Aligarh	012011	1800
Aligarh	022011	1100
Aligarh	032011	1700
Aligarh	042011	2400
Aligarh	052011	2900
Aligarh	062011	2700
Agra	012011	2300
Agra	022011	2600
Agra	032011	1100
Agra	042011	5500
Agra	052011	1600
Agra	062011	3400

4. Now a report is required in the format where the header gets generated on MonthYear dynamically and the values for the Expenses are shown under the  MonthYear and in front of the District. There is a often a requirement of a report like,

DistName	012011	022011	032011	042011	052011	062011
Agra	2300	2600	1100	5500	1600	3400
Aligarh	1800	1100	1700	2400	2900	2700

As and when a new MonthYear is added a new column automatically gets generated and similarly when a new district is added a new row for the report gets created.

5. The raw data (3) can be converted into the report form as in (4) simply by running a stored procedure given in (6) by creating the stored procedure just by running (6) and after successful creation then executing it as follows, 

                  exec PivotDynamicReport 'DistrictData','MonYear','Expense'

               [Sytax: exec PivotDynamicReport 'Table Name','Header Field Name','Data Field Name' ]

              
6. The above stored procedure 'PivotDynamicReport ' can be created as,

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE Procedure [dbo].[PivotDynamicReport]

@SourceTable            nvarchar(50),

@ColumnHeadField  nvarchar(50),

@DataField              varchar(50)

As

BEGIN

Declare     @sql  nvarchar(1000)

declare @Temp table (NewField nvarchar(50))

set @sql = 'Select distinct ' + @ColumnHeadField + ' from ' + @SourceTable

Insert into @Temp(NewField) EXEC sp_executesql @sql

DECLARE @PivotColumnHeaders VARCHAR(100)

DECLARE @PivotTableSQL NVARCHAR(MAX)

SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + cast(NewField as varchar) + ']','[' + cast(NewField as varchar)+ ']')FROM @Temp

SET @PivotTableSQL = N'select * from ' + @SourceTable + ' pivot (Sum (' + @DataField + ') for ' + @ColumnHeadField + ' in ('+ @PivotColumnHeaders +')) as TotalField '

EXECUTE(@PivotTableSQL)

END