Stephen Shakespeare

Transact SQL: Dynamic Pivot

Normal pivot in SQL means pre-defining the pivot column output, unlike Excel which dynamically works out the pivot columns.
A solution to this was found here with thanks to:  http://sqlserver-qa.net/blogs/t-sql/archive/2008/08/27/4809.aspx This copied after comments below with my own naming again here.

Comments

I wanted to join the data from this dynamic pivot to other data relating to the item and for one parameter (ProductID). I created a stored procedure which created a dynamic SQL including these columns and the parameter and passed this SQL to the generic dbo.uspDynamicPivot.

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
(
    selectYear(OrderDate)as pivot_col,e.lastname, o.OrderDate FROM northwind..Employees as e
    INNERJOIN 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 proceduredbo.uspDynamicPivot
(
@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,’)
 

createtable #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)’