Columns to Rows in SQL Server

In this article, you will learn how to convert columns to rows in SQL Server.

We will use unpivot feature for the transformation. Lets' take an example

NOTE: PIVOT/UNPIVOT feature was introduced in SQL Server 2005, so solution will work in SQL Server 2005 onward only.

Let's create a table for Key Value pair and populate some data in it

CREATE TABLE tblkeyvalpair(
    KEY1 INT, KEY2 INT, KEY3 INT, KEY4 INT, KEY5 INT
)
INSERT INTO tblkeyvalpair VALUES (10, 20, 30, 40, 50)

SELECT * FROM tblkeyvalpair

OUTPUT

Here, the keys and values are stored in column form, and our requirement is to display the key and value pair in row form.

Below is the code snippet to achieve the same using unpivot feature

SELECT KeyName, Value
FROM   (
    SELECT * FROM   tblkeyvalpair
) AS t1 
UNPIVOT (
    Value FOR KeyName IN (key1, key2, key3, key4, key5)
) AS t2

If we know all key columns then we can hard code the values as we do in the above solution

But now assume we don't all possible values, in that case we will have to dynamically construct the list of keys and pass it dynamically to the query.

DECLARE @ColumnList VARCHAR(MAX),
        @Query      VARCHAR(MAX),
        @ColumnName VARCHAR(100),
        @TableName  NVARCHAR(100)

SET @TableName = 'tblkeyvalpair'

SELECT @ColumnList = Stuff((SELECT ',' + name
                            FROM   syscolumns
                            WHERE  id = Object_id(@TableName)
                            FOR XML PATH(''))
                     ,1,1,'')

SET @Query = 'SELECT keyname, value FROM(SELECT * FROM ' + @TableName + ') AS T1'
SET @Query = @Query + ' UNPIVOT (value FOR keyname IN (' + @ColumnList + ')) AS T2'

EXEC(@Query)

OUTPUT