How I got an UNPIVOT statement in SQL to run faster

I have data in a SQL database table with columns that need to be transposed into rows. The data is in a Pivot Table / Crosstab format, so I was using the UNPIVOT statement to accomplish this. While it works, I noticed it was a little slow, taking several minutes to run. I wanted to run an experiment in running several INSERT statements.

The source table had a handful of standard columns, and then a column for each month (“2020_08”, “2020_09”, etc). There were several months. I wanted to create a new table that kept the standard columns, but consolidated the many months into two columns: one to indicate the month (“the_month”) and one to indicate the value (“the_hours”).

The UNPIVOT-based version of the SQL was something like this:

Notice that the CASE statement and the IN part of the UNPIVOT listed out each and every month.

The proposed solution was to run an INSERT statement, within a loop.

Specifically:

• Declare and set variable as a date
• Run the INSERT statement
• Add one month to the date; loop until it reaches the end

Of course, I ran into several problems along the way. The first one was that the field names had to dynamically change (“2020_08”). Referencing the variable itself didn’t work. I had to create another variable to store the name of the field; and I dynamically generated the SQL- CASTing the pieces together as a VARCHAR. I ran this STRING using the EXECUTE statement.

The next problem I had was the value for the month had to be surrounded by quotes, so I needed to “escape” the single quote by putting in three quotes. This was caught when doing a PRINT statement of the query (before running the EXECUTE).

The SQL Solution with a loop

The final result?

In this situation, running the several individual INSERT statements was faster than the UNPIVOT statement. It still took several minutes, but it cut down the amount of time by 20%.