MS SQL Multiple Pivots on same field.

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 

Select all 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 

Select all 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