PIVOT, UNPIVOT and Data Reshaping

15.1 PIVOT

SELECT *
FROM (SELECT YEAR(OrderDate) AS Yr, DATENAME(MONTH, OrderDate) AS Mn, LineTotal
      FROM dbo.Orders o JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID) src
PIVOT (SUM(LineTotal) FOR Mn IN ([January],[February],[March],[April])) pvt;

15.2 Conditional Aggregation (often clearer)

SELECT YEAR(OrderDate) AS Yr,
       SUM(CASE WHEN MONTH(OrderDate) = 1 THEN Amt END) AS Jan,
       SUM(CASE WHEN MONTH(OrderDate) = 2 THEN Amt END) AS Feb
FROM dbo.OrderTotals GROUP BY YEAR(OrderDate);

15.3 UNPIVOT

SELECT ProductID, Quarter, Sales
FROM dbo.QuarterlySales
UNPIVOT (Sales FOR Quarter IN (Q1, Q2, Q3, Q4)) u;
-- Alternative: CROSS APPLY (VALUES ...) which keeps NULLs

15.4 Dynamic PIVOT

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(CategoryName), ',') FROM dbo.Categories;
SET @sql = N'SELECT * FROM (SELECT c.CategoryName, YEAR(o.OrderDate) Yr, oi.LineTotal
             FROM dbo.OrderItems oi JOIN dbo.Orders o ON o.OrderID = oi.OrderID
             JOIN dbo.Products p ON p.ProductID = oi.ProductID
             JOIN dbo.Categories c ON c.CategoryID = p.CategoryID) s
             PIVOT (SUM(LineTotal) FOR CategoryName IN (' + @cols + N')) p;';
EXEC sp_executesql @sql;