Unpivot

None of the “simple” examples for an SQL UNPIVOT were in any way simple, so here’s as simple as I can make it.

I’m using the following table definition for this discussion:

CREATE TABLE [dbo].[MyTable](
    [MyHeaderColumn] [varchar](100) NULL,
    [MyColumn1] [int] NULL,
    [MyColumn2] [int] NULL,
    [MyColumn3] [int] NULL
) 

It contains the following data:

output1

But I don’t want to output the data in that format. I want to output all three integer columns in a single column, thusly:

output2

I could use UNION statements, but that’s inefficient and decidedly uncool. UNPIVOT to the rescue!

I’m going to build it up one step at a time, rather than throw it all at you at once. I’m going to be very explicit and verbose. You can slim it down at your leisure.

First you need the source of the data. Simple enough.

    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T

Then you need to encapsulate it so it looks like a single object. In this context, the AS [{name}] is required, since all objects need a name.

(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]

Then you add the UNPIVOT shell, again with the required alias:

(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]
UNPIVOT
(

) AS TR

Then you add the column that will contain the consolidated output values. Note: you’re defining the column here; it doesn’t have to match anything in the source, but it can if you like.

(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]
UNPIVOT
(
    [OutputColumn]
) AS TR

Then we define another output column that’s going to tell us which source column the output value came from. Again, you’re defining the column; it can be named whatever you like.

(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]
UNPIVOT
(
    [OutputColumn] FOR [OutputColumnIdentifier] IN (

                                            )
) AS TR

Then you enumerate the source columns that will be combined in the single output column:

(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]
UNPIVOT
(
    [OutputColumn] FOR [OutputColumnIdentifier] IN (
                                            [Source].[MyColumn1], 
                                            [Source].[MyColumn2], 
                                            [Source].[MyColumn3]
                                            )
) AS TR

At this point, we’ve only defined a data source, so now we need actually define the projection. Note: any columns that you included in the UNPIVOT cannot be included in the output. Think of the UNPIVOT like an aggregate: you can’t output an unaggregated or ungrouped column.

SELECT
TR.[MyHeaderColumn], 
TR.[OutputColumn], 
TR.[OutputColumnIdentifier] 
FROM 
(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]
UNPIVOT
(
    [OutputColumn] FOR [OutputColumnIdentifier] IN (
                                            [Source].[MyColumn1], 
                                            [Source].[MyColumn2], 
                                            [Source].[MyColumn3]
                                            )
) AS TR

And here’s the result:

output3

Notice the contents of the [OutputColumnIdentifier] column. That’s the column the value was retrieved from! Neat.

If you’re not manipulating the original source, you can simplify by just specifying the table directly. This produces the identical output:

SELECT
TR.[MyHeaderColumn], 
TR.[OutputColumn], 
TR.[OutputColumnIdentifier] 
FROM 
[dbo].[MyTable] AS [Source]
UNPIVOT
(
    [OutputColumn] FOR [OutputColumnIdentifier] IN (
                                            [Source].[MyColumn1], 
                                            [Source].[MyColumn2], 
                                            [Source].[MyColumn3]
                                            )
) AS TR

And there you have it! Combining multiple columns into a single column using SQL UNPIVOT.

More information is available in TechNet: Using PIVOT and UNPIVOT, FROM (Transact-SQL).