PIVOT

Use the pivot operator as part of a join. The pivot operator allows you generate columns dynamically based on the list in the FOR subclause.
Example:
Sums freight by customer & year with a column for each employeeid in 1..9
select pivotTable.* from
( select customerid ,year(orderdate) as orderYear,freight,employeeid from orders ) as sourceTable
pivot ( sum( freight) for employeeid in ([1],[2],[3],[4],[5],[6],[7],[8] ,[9])) as pivotTable