O'Reilly logo
  • Dustin Waybright thinks this is interesting:

WITH PivotData AS
(
  SELECT
    custid   , -- grouping column
    shipperid, -- spreading column
    freight    -- aggregation column
  FROM Sales.Orders
)
SELECT custid, [1], [2], [3]
FROM PivotData
  PIVOT(SUM(freight) FOR shipperid IN ([1],[2],[3]) ) AS P;

This query generates the following output (shown here in abbreviated form).

custid  1        2        3
------- -------- -------- --------
1       95.03    61.02    69.53
2       43.90    NULL     53.52
3       63.09    116.56   88.87
4       41.95    358.54   71.46
5       189.44   1074.51  295.57
6       0.15     126.19   41.92
7       217.96   215.70   190.00
8 ...
            
        
			

From

Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012

Note

custid automatically defaults to the row.