How to Convert Rows to Columns in SQL Server Without Using Pivot

Introduction:

Here I will explain how to convert row values to column values without using pivot in SQL Server.

Description:

In previous articles I explained Query to get duplicate records count, Query to remove duplicate records and many articles relating to SQL Server. Now I will explain how to write a query to convert row values to column values without pivot in SQL Server. In database I have one table UserInformation that contain data like as shown below

If you observe above table we have name “Suresh” which repeated for 3 times but it’s having different mobiles number in this case I want to display single username with all mobiles numbers like as shown below

To implement this kind of functionality we need to write the query like as shown below

DECLARE @tempUserInfo TABLE (UserName VARCHAR(50),Mobile VARCHAR(50)) INSERT INTO @tempUserInfo VALUES ('Suresh',9999999999) INSERT INTO @tempUserInfo VALUES ('Suresh',8888888888) INSERT INTO @tempUserInfo VALUES ('Suresh',7777777777) INSERT INTO @tempUserInfo VALUES ('Mahesh',9000000000) INSERT INTO @tempUserInfo VALUES ('Mahesh',9005000000) INSERT INTO @tempUserInfo VALUES ('Madhav',8125000000) INSERT INTO @tempUserInfo VALUES ('Madhav',8160000000) INSERT INTO @tempUserInfo VALUES ('Madhav',8165555000) ;WITH UserDetails AS (   SELECT *,ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY UserName) AS ColumnNumber FROM @tempUserInfo ) SELECT DISTINCT t.UserName ,t1.Mobile AS website1,t2.Mobile AS website2,t3.Mobile AS website3 FROM @tempUserInfo t LEFT OUTER JOIN UserDetails t1 ON t.UserName=t1.UserName AND t1.ColumnNumber=1 LEFT OUTER JOIN UserDetails t2 ON t.UserName=t2.UserName AND t2.ColumnNumber=2 LEFT OUTER JOIN UserDetails t3 ON t.UserName=t3.UserName AND t3.ColumnNumber=3 Order by t.userName desc

Above query will change row values to column values and the ouptput like as shown below

Output

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email