We help IT Professionals succeed at work.

MS SQL Multiple Pivots on same field.

EdFinn
EdFinn asked
on
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

View Solution Only

Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Having Rev and Units on separate rows.... is a strange way to return a SQL Server resultset, because result set rows usually have the same context.
Anyway, give this a try.

SELECT 'Rev' pivottype, *
FROM (
      SELECT item_id, fiscal_period, revenue
      FROM db
      WHERE item_id is not null
      ) p
PIVOT
(
      SUM(revenue)
      FOR fiscal_period
      IN ([1], [2], [3], [4], [5], [6])
) as revpiv
union all
SELECT 'Units', *
FROM (
      SELECT item_id, fiscal_period, units
      FROM db
      WHERE item_id is not null
      ) p
PIVOT
(
      SUM(units)
      FOR fiscal_period
      IN ([1], [2], [3], [4], [5], [6])
) as revpiv
order by item_id, pivottype desc
Lowfatspread
Top Expert 2011

Commented:
have you read this?

http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Broaden-Your-Horizons-Pivot-Again.html

try it this way...


;with mycte as (select item_id,'r'+convert(varchar,fiscal_period) as rev_per
,'U'+convert(varchar,fiscal_period) as U_per
,revenue,units
 from table where ....)
Select p1.item_id,r1,u1,r2,u2,r3,u3,r4,u4,r5,u5,r6,u6
 from (select item_id,revenue,rev_per from cte) as r
pivot sum(revenue) for rev_per in (r1,r2,r3,r4,r5,r6) as p1
inner join (select item_id,units , u_per from cte) as U
pivot sum(units) for u_per in (u1,u2,u3,u4,u5,u6) as p2
 on p1.item_id=p2.item_id
order by 1
EdFinn

Author

Commented:
Ahh, Unions never came to mind.

I added a few fields to the sub select to get class and item_description, wrapped the whole thing inside another select, then pulled columns in order and did a order by.

Now just to loop though the data in C# and null out duplicate fields.

Thanks for the help.
SELECT item_subclass_id as Class, item_id, item_description, Data, [1], [2], [3], [4], [5], [6]
FROM (
	SELECT 'Rev' Data, *
	FROM (
		  SELECT item_id, item_subclass_id, item_description, fiscal_period, revenue
		  FROM (db)
		  WHERE (where)
		  ) p
	PIVOT
	(
		  SUM(revenue)
		  FOR fiscal_period
		  IN ([1], [2], [3], [4], [5], [6])
	) as revpiv
	union all
	SELECT 'Units' Data, *
	FROM (
		  SELECT item_id, item_subclass_id, item_description, fiscal_period, units
		  FROM (db)
		  WHERE (where)
		  ) p
	PIVOT
	(
		  SUM(units)
		  FOR fiscal_period
		  IN ([1], [2], [3], [4], [5], [6])
	) as revpiv
) tab
ORDER BY item_subclass_id, item_id, item_description, Data desc

Open in new window