Sql Server Pivot Table

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Renju.R
-- Create date: August 5,2008
-- Description: Cross Tabbing Tables
-- =============================================
ALTER procedure [dbo].[crosstab]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
as
begin
declare @sql varchar(8000), @delim varchar(1)
set nocount on
set ansi_warnings off
exec ('select ' + @pivot + ' as pivot into ##pivot from ' + @table + ' where 1=2')
exec ('insert into ##pivot select distinct ' + @pivot + ' from ' + @table + ' where '
+ @pivot + ' is not null')
select @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' end)' )
select @delim=case sign( charindex('char', data_type)+charindex('date', data_type) )
when 0 then '' else '''' end
from tempdb.information_schema.columns
where table_name='##pivot' and column_name='pivot'
select @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' case ' + @pivot + ' when '
+ @delim + convert(varchar(100), pivot) + @delim + ' then ' ) + ', ' from ##pivot
drop table ##pivot
select @sql=left(@sql, len(@sql)-1)
select @select=stuff(@select, charindex(' from ', @select)+1, 0, ', ' + @sql + ' ')
exec (@select)
end
set ansi_warnings on

For Executing the procedure some Examples are as below


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[EvaluationFormWise]
as
begin
execute crosstab
'select pm.ProjectName,mm.ModuleName,fm.formName,fm.formType,
sm.SeverityName from bugmaster bm
inner join formmaster fm on fm.formId=bm.formid
inner join dbo.SeverityMaster sm on sm.SeverityID=bm.SeverityID
inner join dbo.ProjectMaster pm on pm.projectId=bm.projectId
inner join dbo.ModuleMaster mm on mm.moduleId=bm.moduleid
left outer join UserRegMaster um on um.userid=bm.programmerId
left outer join UserRegMaster urm on urm.userid=bm.submitterid
group by pm.ProjectName,mm.ModuleName,fm.formName,fm.formType,sm.SeverityName',
'count(bm.SeverityID)',
'SeverityName',
'SeverityMaster'
end