Is UNPIVOT the best way for converting columns into rows?

October 30th, 2013

Convertion of columns into rows is widely used in real tasks. In this article we will talk about different implementations of T-SQL UNPIVOT transformations.

Let’s assume we have an aggregated table which contains data about results of the games played by each player. We have a task to convert columns into rows.

There are several ways to accomplish this task, so let’s take a look at the execution plans of each of the suggested below implementations. For this we will use SQL Profiler available in dbForge Studio for SQL Server.

To receive execution plans automatically each time the query is executed we need to switch to the profiling mode:

profiling mode

It’s also possible to get the query plan without starting its execution. For this, the Generate Execution Plan command must be ran.

Let’s Start!

SQL Query profiler


SQL Server 2000 did not offer an efficient way to convert columns into rows. Because of that multiple reading from the same table with a different set of columns united through the UNION ALL statements was widely used:

The crucial weakness of this practice is the multiple data reading, which considerably decreases the efficiency of the query execution.

It is obvious, when we look at the execution plan of the following query:

query execution plan


When SQL Server 2005 hit the stage, a new syntax T-SQL structure was introduced – UNPIVOT.
Let’s simplify the previous query with the UNPIVOT statement:

As a result of the query execution we get the following execution plan:

result of execution plan


However, progress does not stand still, because after the release of SQL Server 2008 the areas of use of the VALUES statement expanded.

Query which uses the VALUES statement will look like this:

Moreover, the execution plan will be simpler, comparing to UNPIVOT:

comparing execution plan to UNPIVOT

4. Dynamic SQL

Using the dynamic SQL allows creating general-purpose query for any table on the condition that columns not included in the primary key have a compatible data-type between them:

And the result is the following query:

This method is less fast because the automatic generation of the UNPIVOT query requires an additional reading from the system views and rows concatenation via XML trick.

5. XML

A more smarter way to execute dynamic UNPIVOT is by making a little trick with XML:

In the query above, for each row SQL Server has generated a specified XML:

Afterwards the name of the attribute and its value are parsed. In most cases the use of XML results into a more slow execution plan. In our case that is the price we pay for it being universal.

Let’s compare the results by executing Compare Selected Results command:

Compare Selected Results command

Let’s note that there is no obvious difference in the speed of execution of UNPIVOT and VALUES queries. This is valid for the cases when simple transformation of columns into rows is taking place.

Let’s examine another task, where we need to find the most frequent outcome of the game for each player.

We’ll try to do this task with the help of the UNPIVOT statement:

In the execution plan its show that the bottleneck is the multiple data reading and sorting, which is necessary for organizing the data rows:

data rows organizing

It’s easy to get rid of the multiple data reading if we remember that columns from the external query block can be used:

Multiple reading of data was eliminated but the most resource consuming operation – sorting is still there:

sorting operation

And that is how the VALUES statement behaves during this task:

As we’ve expected the plan was simplified, however sorting is still present

simplified execution plan

Let’s try to avoid sorting by using the aggregation function:

Now the execution plan looks like this:

final execution plan

Hurrah! We managed to get rid of sorting.


In the cases, when we need to carry out a simple convertion of columns into rows in SQL Server it is better to use UNPIVOT or VALUES structures.

If after the convertion the received data rows should be used for aggregation or sorting, then we should rather use VALUES structure which, in most cases, results into more efficient execution plans.

For the tables, where different structure types might occur and the number of columns is unrestricted, it is recommended to use XML which unlike the dynamic SQL can be used inside the table functions.

Step-by-step analysis of the execution plan with the help of SQL Profiler in dbForge Studio for SQL Server allows detecting bottlenecks in the query’s performance.

Studio for SQL Server download


To adapt some examples according to the issues of SQL Server 2005, VALUES statements:

we need to replace on SELECT UNION ALL SELECT combination:

9 Responses to “Is UNPIVOT the best way for converting columns into rows?”

  1. kevin Says:

    Hi Sergey Syrovatchenko , how do I use you example to reverse the unpivot method/s to go back to pivot ?

  2. Raghvendra Says:

    Please help me with the below query, I want column_Name to be displayed in rows and and Table_Name and database are grouped as all the column for a table will be displayed in single row:

    IF OBJECT_ID(‘Tempdb.dbo.#temptbl’, ‘u’) IS NOT NULL
    DROP TABLE #temptbl

    Create Table #tempTbl
    Database_Name nvarchar(100),
    Table_Name nvarchar(100),
    Column_Name nvarchar(100)

    Insert Into #tempTbl
    Select DB_Name() , sys.schemas.NAME + ‘.’ + sys.tables.NAME, FROM sys.tables
    INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
    Inner Join sys.columns On sys.tables.object_id = sys.columns.object_id

    Select * from #tempTbl

  3. Ahmed Shahin Says:

    I’m having a Table called Test_Pivot which looks like
    F_SN F_Title F_Data
    1001 TRX RRN 301807460001.00
    1001 DATE TIME 18:07 ,30/04/2015
    1001 AUTH CODE
    1001 AMOUNT 0.22
    1011 TRX RRN 301920050002.00
    1011 DATE TIME 19:20 ,30/04/2015
    1011 AUTH CODE
    1011 AMOUNT 0.01

    The output should look like this:

    301807460001.00 18:07 ,30/04/2015 0.22 SPAN DECLINED
    301920050002.00 19:20 ,30/04/2015 0.01 MASTERCARD DECLINED

  4. Andrey Langovoy Says:

    Hi Ahmed,
    Please clarify your question!
    Thank you.

  5. Kai Says:


    How did you get the query profiler results shown in image 06? What tool did you use?

  6. Sergey Syrovatchenko Says:

    Thanks for the comment. This is a built-in feature in dbForge Studio profiler

  7. Kai Says:

    Can dbForge Studio profile monitor the query’s tempdb and memory usage?

  8. Sergey Syrovatchenko Says:

    Hi Kai, you mean spills into tempdb? If yes, in this case we have identical functionality as in SSMS. In future it’s planned to expand this functionality.

  9. André St-Onge Says:

    I tried 2 differents way with using UNPIVOT and CROSS APPLY with VALUES and my results on 41,894,370 of rows is UNPIVOT is better but not very much
    Using UNPIVOT 31:38 minutes
    Using CROSS APPLY with VALUES 33:26 minutes
    CROSS APPLY spent 3 minutes more than UNPIVOT
    My SQL is : Microsoft SQL Server Enterprise (64-bit) version : 11.0.5532.0

Leave a Comment