Often when trying to pivot data you wont know what the possible values that you need to pivot on, in this case you can use dynamic SQL.
Lets say we have a table with employee sale counts
We can pivot this data using the standard Pivot syntax like this
The problem is as new employees come into our table this query wont pick them up as we’ve not explicitly named them. We can get round this by using dynamic SQL with the Pivot statement…
The above query will build a dynamic Pivot SQL statement which will get all possible employee values from the EmployeeSales table and pivot on that. With this you can easily customize the query to limit which employees get pivoted on, for example only the top 2 performers…