Convert Rows to Columns in SQL Server Without Using Pivot

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