SQL Server crosstab queries

SQL Server crosstab queries

A correspondence between the two constructs, from Access and SQL Server, is shown in the following table:
comparing Access SQL to TSQL

TRANSFORM Sum([UnitPrice]*[quantity]*(1-[discount])) AS Total
SUM(CASE MONTH(dbo.Orders.OrderDate)
WHEN 1 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)
ELSE 0 END) AS Jan,


SELECT Year([OrderDate]) AS order_year
SELECT TOP 100 PERCENT YEAR(dbo.Orders.OrderDate) AS order_year

FROM [Order Details] RIGHT JOIN Orders ON [Order Details].OrderID = Orders.OrderID
FROM dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

GROUP BY Year([OrderDate])
GROUP BY YEAR(dbo.Orders.OrderDate)

PIVOT Month([OrderDate]) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
CASE MONTH(dbo.Orders.OrderDate)
WHEN 1 THEN …
ELSE 0 END