Cacher is the code snippet organizer for pro developers

/*

PIVOTing and UNPIVOTing allows you to change the orientation of the results set. This task can be done easily in other programs such as Excel (and probably SSRS) so you don't have to always do this using T-SQL, but it is possible.

PIVOT - rotates data from rows-based orientation to a columns-based orientation.

UNPIVOT - rotates data from a columns-based orientation to a rows-based orientation. Spreads or splits values from one source row into one or more target rows. Each source row becomes one or more rows in a result set based on number of columns being pivoted. If you're unpivoting pivoted-data, you're only getting data you can see in the pivoted data. In other words, if you pivot data and aggregate, and then unpivot back to the original orientation, you might loose some of the detail that you had before aggregating the data during the original pivot.


The syntax for PIVOT:

SELECT <non-pivoted column>,

    [first pivoted column] AS <column name>,

    [second pivoted column] AS <column name>,

    ...

    [last pivoted column] AS <column name>

FROM

    (<SELECT query that produces the data>)

    AS <alias for the source query>

PIVOT

(

    <aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

    IN ( [first pivoted column], [second pivoted column],

    ... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

*/

-- Example of non-PIVOTed query
SELECT LastName, ManagerID FROM dbo.MyEmployees;

/*

Result from the non-PIVOTed query above:

LastName     ManagerID
------------ -----------
Sánchez      NULL
Bradley      273
Gibson       16
Welcker      1
Jiang        273
Blythe       274
Mitchell     274
Abbas        273
Tsoflias     285

(9 row(s) affected)

*/

-- a PIVOTed query, taking the values in the LastName column and PIVOTing them so they become columns, with the Manager ID values listed in a single row. Note that when telling it what the new PIVOTed columns will be named, you're required to fill in the values in the orignal column (ie you're required to know the values of that column before you're able to pivot it and make those values the basis for the new PIVOTed columns).
SELECT 
'ManagerID' AS Sorted_by_Employee,
[Sánchez],
[Bradley],
[Gibson],
[Welcker],
[Jiang],
[Blythe],
[Mitchell],
[Abbas],
[Tsoflias]
FROM (SELECT LastName, ManagerID FROM dbo.MyEmployees) AS SourceTable
PIVOT
(
	MAX(ManagerID)
	FOR LastName IN ([Sánchez],
		[Bradley],
		[Gibson],
		[Welcker],
		[Jiang],
		[Blythe],
		[Mitchell],
		[Abbas],
		[Tsoflias])
) AS PivotTable;

/*

Result from query above:

Sorted_by_Employee Sánchez  Bradley  Gibson  Welcker  Jiang  Blythe  Mitchell  Abbas  Tsoflias
------------------ -------- -------- ------- -------- ------ ------- --------- ------ ---------
ManagerID          NULL     273      16       1        273   274     274       273    285

(1 row(s) affected)

*/

------------------------------
-- DETAIL OF PIVOT QUERY!!! --
------------------------------

-- SELECT statement that defines the source table
SELECT
-- start by selecting a text string which will be the row header (label) for the new PIVOTed row that will be created
'ManagerID' AS Sorted_by_Employee,
-- the column headers for the new columns to be created in the PIVOTed row. This takes the values in the LastName column from the derived table and spells them out as distinct columns in the PIVOTed table to be created. Note that you need to have knowledge of what values will be present in the LastName column of the derived column so you can spell them out.
[Sánchez],
[Bradley],
[Gibson],
[Welcker],
[Jiang],
[Blythe],
[Mitchell],
[Abbas],
[Tsoflias]
-- the derived table, given the alias SourceTable
FROM (SELECT LastName, ManagerID FROM dbo.MyEmployees) AS SourceTable
-- PIVOT keyword which begins creation of the PIVOTed table
PIVOT
(
-- the column being aggregated must use an aggregate function. In this case, the ManagerID column from the derived table will be placed into a single row - therefore you need to aggregate the values so a single value can fit in the one row for each new column created. In this case, MAX is being used since there's only one value to start with (if there are multiple values, you can use aggregate functions liek AVG, SUM, exc.)
	MAX(ManagerID)
-- the column in the derived table where you want to PIVOT the row values into separate columns in the PIVOTed table
	FOR LastName 
-- IN list where you list the values in the column-to-be-PIVOTed: these values will become column headers in the PIVOTed table
	IN ([Sánchez],
		[Bradley],
		[Gibson],
		[Welcker],
		[Jiang],
		[Blythe],
		[Mitchell],
		[Abbas],
		[Tsoflias])
) 
-- giving the PIVOTed table an alias of PivotTable
AS PivotTable;