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 which can be used in SQL Server 2000

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 year(from Employees and Orders table from Northwind databases)

Usage :

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(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)'

Published 27 August 2008 13:31 by Madhivanan

Comments

# Splitting delimited data to columns - Set based approach

11 September 2008 15:58 by Madhivanan

In this post splitting-csv-to-columns I showed how to split delimited data into multiple columns Simon

# Splitting delimited data to columns - Set based approach

11 September 2008 16:01 by SQL Server Transact-SQL (SSQA.net)

In this post splitting-csv-to-columns I showed how to split delimited data into multiple columns Simon

# re: Dynamic PIVOT in SQL Server 2005

21 January 2009 13:43 by paulholzweber

Hi!

That sounds veryvery useful! I tried to get that running, but i have difficulties.

I use that table as input:

F1 F2 F3

A1 x 1

A1 y 2

A1 z 3

A2 x 4

A2 y 5

A2 z 6

A3 x 7

A3 y 8

A3 z 9

Then I create the procedure... and then I try to run it like that:

EXEC dynamic_pivot

'SELECT F1 FROM input',

'F2',

'max(F3)'

Unfortunately I get an error message:

(3 row(s) affected)

Msg 207, Level 16, State 1, Line 8

Invalid column name 'F3'.

Can you please help me?

Thanks

Paul

# re: Dynamic PIVOT in SQL Server 2005

21 January 2009 15:23 by Madhivanan

paulholzweber ,

You should execute like

EXEC dynamic_pivot

'SELECT F1,f3 FROM input',

'F2',

'max(F3)'

# Pivot for unknown values | keyongtech

18 February 2009 14:07 by Pivot for unknown values | keyongtech

Pingback from  Pivot for unknown values | keyongtech

# re: Dynamic PIVOT in SQL Server 2005

28 April 2009 14:05 by lcarpay

Hi Madhivanan, do you have a clue how to INSERT INTO the result of this fantastic SP in a new table. I thought about making a function of your SP but as the columns are not know a design time I see no way to define such a udf.

Can you help me out?

gr leon

# re: Dynamic PIVOT in SQL Server 2005

28 April 2009 15:34 by Madhivanan

gr leon,

Refer method 2

sqlblogcasts.com/.../select-columns-from-exec-procedure-name-is-this-possible.aspx

# re: Dynamic PIVOT in SQL Server 2005

10 November 2009 18:07 by qzack

Made some changes to suit my tables and this worked perfectly first time!

Fantastic,

Paul