PIVOT is an extremely efficient way to alter the structure of a result set, expanding a single column of values into a set of separate columns. UNPIVOT does the exact opposite, taking a set of columns and resolving them into a single output column. Both of these operators can be very useful in reporting, analytics, or when trying to format existing data into a specific structure as required by an application.
A significant limitation of both operators is that the column or name list for each must be defined prior to runtime in your TSQL. This can be acceptable if the name list is static or predictable enough ...
