Apr 22, 2011

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(
INSERT INTO tblkeyvalpair VALUES (10, 20, 30, 40, 50)

SELECT * FROM tblkeyvalpair

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 
    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.
        @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(''))

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


    Choose :
  • OR
  • To comment
No comments:
Write Comments

Probabilmente come studente universitario ti troverai a scrivere una o più tesi per il tuo analogo esame di fine carriera. Ma di cosa si tratta in particolare? Scopriamo quali sono i pro dei tool per riscrivere testo che troverai online. Per strumento di parafrasi si intende un tool online che si occupa di parafrasare in maniera immediata frasi oppure interi paragrafi. Questa attività si svolgerà interamente su internet e non lascerà alcuna traccia di plagio