This example shows how to Pivot the query based on ‘Sales Year’ using an Inline View/Inner Query
From this:
To this:
-- 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
Advertisements