How to transform multiple columns to rows in SQL Server

Problem: How to Transform columns to rows in SQL Server.

Solution:

UNPIVOT, SQL Server 2005 or Later.

UNPIVOT rotates a table-valued expression by turning the column in the expression into multiple rows in the output, It’s The reverse of what PIVOT does.

UNPIVOT is generally used where we need some normalization of data.

Example 1:

In this article I am going to show you a basic example of how to use UNPIVOT.

I would like to create a table and some demoralized column structure and add some dummy data.

I have two phone numbers for each user and let’s transform these two phone numbers into one single column.

Create Table dbo.TUsers

(

UserId INT IDENTITY(1,1),

UserName Varchar(10),

PrimaryAddress Varchar(1000),

SecondaryAddress Varchar(1000),

PrimaryPhone Varchar(15),

SecondaryPhone Varchar(15),

)

INSERT INTO dbo.TUsers (Username, PrimaryAddress, SecondaryAddress,PrimaryPhone, SecondaryPhone)

VALUES

('Alex','Sacramento,CA','Dallas,TX','343-567-1254','123-456-7890'),

('Mari','Orange,CA','Detroit,MI','123-421-2414','423-432-8124'),

('Chris','Erie,PA','Newyork,NY','351-537-3255','535-124-1241'),

('Peter','Houston,TX','Dallas,TX','352-535-1522','543-452-4114')

Select  userid,username,PrimaryAddress,SecondaryAddress, Phone,Phones

FROM

(

  SELECT userid, Username, PrimaryAddress , SecondaryAddress ,PrimaryPhone, SecondaryPhone 

  FROM dbo.Tusers

) AS cp

UNPIVOT

(

  Phone FOR Phones IN (PrimaryPhone, SecondaryPhone)

  ) AS up

  GO

Output:

Example 2:

Let’s go further north and UNPIVOT Multiple columns, I am going to use the same table which created earlier in this article show you how to flip address as well.

The Real trick here is to do an alias two match the last one character of each column I am doing an alias

As follows.

PrimaryAddress	Address_1	PrimaryPhone	Phone_1
SecondaryAddress	Address_2	SecondaryPhone	Phone_2

Select  userid,username, Phone,Phones,Address,T_Address

FROM

(

  SELECT userid, Username, PrimaryAddress as Address_1, SecondaryAddress as Address_2, PrimaryPhone as Phone_1, SecondaryPhone  as Phone_2

  FROM dbo.Tusers

) AS cp

UNPIVOT

(

  Phone FOR Phones IN (Address_1, Address_2)

  ) AS up

UNPIVOT

(

  Address FOR T_Address IN ( Phone_1, Phone_2)

) AS up1

WHERE RIGHT(Phones,1)=RIGHT(T_Address,1) -- This part makes the match of columns and alias.

Output: