We help IT Professionals succeed at work.
Get Started

MS SQL Multiple Pivots on same field.

4,466 Views
Last Modified: 2012-05-09
I'm working on moving a report from a excel document to a web report.

The excel document works off a datasource from our database and the pivot table in in aggregates on 3 of the fields.

I'm looking to see if their is a way to mimic the excel results purely in SQL.

I was able to pivot on 1 value with the below code:

SELECT *
FROM (
	SELECT item_id, fiscal_period, revenue
	FROM (db) 
	WHERE (whereclause)'
	) p
PIVOT
(
	SUM(revenue)
	FOR fiscal_period
	IN ([1], [2], [3], [4], [5], [6])
) as revpiv
order by item_id 

Open in new window


But as soon as I try to add another pivot the statement dies.

SELECT *
FROM (
	SELECT item_id, fiscal_period, revenue, units
	FROM (db)
	WHERE (whereclause)'
	) p
PIVOT
(
	SUM(revenue)
	FOR fiscal_period
	IN ([1], [2], [3], [4], [5], [6])
) as revpiv
PIVOT
(
	SUM(units)
	FOR fiscal_period
	IN ([1], [2], [3], [4], [5], [6])
) as unpiv
order by item_id 

Open in new window


I'm getting the error.
The column name "1" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
and
Invalid column name 'fiscal_period'.
and
The column '1' was specified multiple times for 'unpiv'.

Which makes it seem to me the issue is pivoting multiple fields based on the same value and also that the fiscal_period field doesn't exist past the first pivot.

The end goal is something similar to the attached image. I know that result isn't possible without doing some modification in code outside the db but I'm curious how close I can get the results to that format.

So the question is, does the pivot command in SQL have enough functionality for this to be done? and if so how do I do it.

P.S.: I'm already working on another way to solve this which is working quite well so please limit your answers to SQL and the pivot command, Thanks!

Also sorry if anything is confusing I wrote this over 2 hours while working on other things so if you need clarification just ask.
piv.png
Comment
Watch Question
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE