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.
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 (,,,,,,, ,)) as pivotTable