Pivoting a dynamic set

SQL Server does not allow for a dynamic set, which can be problematic if you need to pivot based on the data that comes from rows and you can have various values in the column.

In general, you need a stored procedure and dynamic SQL in order to handle the dynamic pivots. This requires uses of metadata such as sys.columns table. Thus in general the steps needs to be:

  • Define the columns that needs to be present in the SELECT
  • Define the columns that will create the new pivoted columns, usually a SELECT over the original column containing the data that needs to be pivoted.
  • Assemble the dynamic SQL
  • Execute the dynamic SQL, returning the result set.