I am facing the following challenge. I need to rotate table data twice over the same column. Here's a screenshot of the data.
I want to have one row for each Item ID containing both the purchasing value and the selling value for each year. I tried doing this by selecting the "year" column twice, formatting it a bit so each selling year gets prefixed with a "S" and each purchasing year begins with a "P", and using 2 pivots to rotate around the 2 year columns. Here's the SQL query (used in SQL Server 2008):
SELECT [Item ID], [P2000],[P2001],[P2002],[P2003], [S2000],[S2001],[S2002],[S2003] FROM ( SELECT [Item ID] ,'P' + [Year] AS YearOfPurchase ,'S' + [Year] AS YearOfSelling ,[Purchasing value] ,[Selling value] FROM [ItemPrices] ) AS ALIAS PIVOT ( MIN ([Purchasing value]) FOR [YearOfPurchase] in ([P2000],[P2001],[P2002],[P2003]) ) AS pvt PIVOT ( MIN ([Selling value]) FOR [YearOfSelling] in ([S2000],[S2001],[S2002],[S2003]) ) AS pvt2
The result is not exactly what I was hoping for (see image below):
As you can see, there are still more than one row for each item ID. Is there a way to reduce the number of rows to exactly one per item? So that it looks a bit like the Excel screenshot below?
TSQL doesn't support multiple aggregates in PIVOT.
You can use the SQL Pivot statement to transpose multiple columns.
You can create multiple columns or rows in a pivot table to handle multiple descriptions.
My suggestion would be to apply both the UNPIVOT
and the PIVOT
functions to get the result.
The UNPIVOT
will turn the PurchasingValue
and SellingValue
columns into rows. Once this is done, then you can pivot the data into your result.
The code will be:
select * from ( select itemid, case when col = 'PurchasingValue' then 'P' when col = 'SellingValue' then 'S' end + cast(year as varchar(4)) new_col, value from yourtable unpivot ( value for col in ([PurchasingValue], [SellingValue]) ) unpiv ) src pivot ( max(value) for new_col in (P2000, P2001, P2002, P2003, S2000, S2001, S2002, S2003) ) piv;
See SQL Fiddle with Demo. The result is:
| ITEMID | P2000 | P2001 | P2002 | P2003 | S2000 | S2001 | S2002 | S2003 | -------------------------------------------------------------------------- | 1 | 1000 | 1100 | 1200 | 1300 | 900 | 990 | 1080 | 1170 | | 2 | 500 | 550 | 600 | 650 | 450 | 495 | 540 | 585 |
In SQL Server 2008+ you can use CROSS APPLY
with VALUES
along with the PIVOT
function:
select * from ( select itemid, col+cast(year as varchar(4)) new_col, value from yourtable cross apply ( VALUES (PurchasingValue, 'P'), (SellingValue, 'S') ) x (value, col) ) src pivot ( max(value) for new_col in (P2000, P2001, P2002, P2003, S2000, S2001, S2002, S2003) ) piv
See SQL Fiddle with Demo
One easy way to pivot multiple columns is to just use Aggregate(Case) expressions.
SELECT [Item ID], [P2000] = SUM(CASE WHEN [Year] = 2000 THEN [Purchasing value] END), [P2001] = SUM(CASE WHEN [Year] = 2001 THEN [Purchasing value] END), [P2002] = SUM(CASE WHEN [Year] = 2002 THEN [Purchasing value] END), [P2003] = SUM(CASE WHEN [Year] = 2003 THEN [Purchasing value] END), [S2000] = SUM(CASE WHEN [Year] = 2000 THEN [Selling value] END), [S2001] = SUM(CASE WHEN [Year] = 2001 THEN [Selling value] END), [S2002] = SUM(CASE WHEN [Year] = 2002 THEN [Selling value] END), [S2003] = SUM(CASE WHEN [Year] = 2003 THEN [Selling value] END) FROM ItemPrices GROUP BY [Item ID]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With