Dynamic PIVOT in SQL Server 2005

CREATE PROCEDURE dynamic_pivot

(

@select varchar(2000),

@PivotCol varchar(100),

@Summaries varchar(100)

)

AS

declare @pivot varchar(max), @sql varchar(max)

select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')

create table #pivot_columns (pivot_column varchar(100))

Select @sql='select distinct pivot_col from ('+@select+') as t'

insert into #pivot_columns

exec(@sql)

select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns

select @sql=

'select * from('+@select+') as t

pivot

('+@Summaries+' for pivot_col in ('+@pivot+')) as p'

exec(@sql)

Select A.AddNo,B.Subject,A.Marks

From tbl_MarkDetails AS A, tbl_SubCode AS B

Where A.SubjectCode=B.SubjectCode

EXEC dynamic_pivot

'Select A.AddNo,coalesce(A.Marks,0) as Marks

From tbl_MarkDetails AS A, tbl_SubCode AS B

Where A.SubjectCode=B.SubjectCode',

'Subject',

'SUM(Marks)'

Drop Table tbl_SubCode

Drop Table tbl_MarkDetails

Purpose : Find total sales made by each person for each year(from Persons and Orders table)

Usage :

EXEC dynamic_pivot
'SELECT e.lastname, o.OrderDate FROM Persons as e
INNER JOIN Orders as o ON (e.P_ID=o.P_ID) ',
'Year(OrderDate)',
'Count(OrderDate)'

Purpose : Find total sales made by each company for each product (from products, order details and suppliers table from Northwind database)

Usage :

EXEC dynamic_pivot
'SELECT s.companyname,coalesce(od.unitprice*od.quantity ,0) as total_cost 

FROM northwind..products as p
inner join northwind..[order details] as od on p.productid=od.productid
inner join northwind..suppliers as s on p.supplierid=s.supplierid',
'productname',
'sum(total_cost)'