Dynamic PIVOT in SQL Server 2005
The PIVOT operator available in SQL Server 2005 is used to generate the Cross-tab results
Consider this example
select
* from
(
select Year(OrderDate) as pivot_col,e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID)
) as t
pivot
(
Count(OrderDate) for pivot_col in ([1996],[1997])
) as p
which shows total orders of each employees for years 1996 and 1997
What if we want to have this for all the years available in the table
You need to use dynamic sql
This procedure is used to generate Dynamic Pivot results
The approach is very similar to my Dynamic Crosstab with multiple PIVOT Columns blog post
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)
Purpose : Find total sales made by each employee for each yearUsage :
EXEC
dynamic_pivot
'SELECT e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) ',
'Year(OrderDate)',
'Count(OrderDate)'
Purpose : Find total sales made by each company for each product
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)'