PIVOT and UNPIVOT
PIVOT and UNPIVOT can be used to change a table-valued expression into another table.
PIVOTPIVOT rotates a table-value expression by turning unique values from one column into multiple columns in the output. PIVOT can run aggregations where required on any column value.
"SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, , , , ,  FROM ( SELECT DaysToManufacture, StandardCost FROM Production.Product ) AS SourceTable PIVOT ( AVG(StandardCost) FOR DaysToManufacture IN (, , , , ) ) AS PivotTable;"
UNPIVOTUNPIVOT carries out nearly the opposite to PIVOT by rotating columns of a table-valued expressions into column values.
"SELECT VendorID, Employee, Orders FROM (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) p UNPIVOT (Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5) )AS unpvt;"
For further information on PIVOT and UNPIVOT, see FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)