Pivot derived columns using case statements

Posted by

0

This example shows how to Pivot the query based on ‘Sales Year’ using an Inline View/Inner Query

From this:

Pivot Derived Columns Image 1

To this:

Pivot Derived Columns Image 2

-- Create Table Orders
Create Table Orders
(
OrderID int NOT NULL IDENTITY ,
Country nvarchar(50) NOT NULL,
OrderDate datetime NOT NULL,
Amount decimal(10,2) NOT NULL,
PRIMARY KEY (OrderID)
)
GO
 
--Insert Sample Values into Orders
--Date Format as used by SQL Server- Refer to BLOG post Date Handling for further info

INSERT INTO Orders(Country,OrderDate,Amount)
VALUES
('USA',Convert(datetime,'10/20/2012',101),120.00), 
('Australia',Convert(datetime,'11/15/2012',101),80.00),
('USA',Convert(datetime,'06/23/2013',101),75.00),
('Japan',Convert(datetime,'08/21/2013',101),64.00),
('Japan',Convert(datetime,'10/10/2013',101),120.00),
('USA',Convert(datetime,'10/27/2013',101),53.00);
GO

Final Query

select Country,Sum(Tot2012) as '2012 Sales',Sum(Tot2013) as '2013 Sales'
from (	
	select Country,
	CASE When DATENAME(year,OrderDate) = '2012'
	then Amount else 0 end as Tot2012,
	CASE When DATENAME(year,OrderDate) = '2013' 
	then Amount else 0 end as Tot2013
	from Orders) OrdFlipped
group by Country