Bits of .NET

Displaying row data as columns manually requires complex CASE statements. PIVOT does it automatically.

Source Data:

Month      Product   Sales
January    Widget    100
January    Gadget    150
February   Widget    120
February   Gadget    180

PIVOT Query:

SELECT *
FROM (
    SELECT Month, Product, Sales
    FROM SalesData
) AS SourceTable
PIVOT (
    SUM(Sales)
    FOR Product IN ([Widget], [Gadget])
) AS PivotTable;

Result:

Month      Widget   Gadget
January    100      150
February   120      180

Dynamic Columns:

DECLARE @columns NVARCHAR(MAX);
SELECT @columns = STRING_AGG(QUOTENAME(Product), ',')
FROM (SELECT DISTINCT Product FROM SalesData) AS Products;

DECLARE @sql NVARCHAR(MAX) = '
SELECT * FROM SalesData
PIVOT (SUM(Sales) FOR Product IN (' + @columns + ')) AS PVT';

EXEC(@sql);

Perfect for reports and cross-tabulation!