Dynamic SQL: Applications, Performance, and Security in Microsoft SQL Server

© Edward Pollack 2019

Edward PollackDynamic SQLhttps://doi.org/10.1007/978-1-4842-4318-3_9

9. Dynamic Pivot and Unpivot

Edward Pollack¹ 

(1)

Albany, NY, USA

 

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 our TSQL. This can be acceptable if the name list is static or predictable ...