Convert Rows to Columns in SQL Server Without Using Pivot
DECLARE @UserData TABLE (UserName VARCHAR(50),Mobile VARCHAR(50))
INSERT INTO @UserData VALUES (‘Abdul’,23243434)
INSERT INTO @UserData VALUES (‘Abdul’,345435345)
INSERT INTO @UserData VALUES (‘Abdul’,23423)
INSERT INTO @UserData VALUES (‘Raj’,2345435)
INSERT INTO @UserData VALUES (‘Raj’,324324)
INSERT INTO @UserData VALUES (‘Raj’,234234)
INSERT INTO @UserData VALUES (‘Manish’,3453453)
INSERT INTO @UserData VALUES (‘Manish’,345345)
INSERT INTO @UserData VALUES (‘Manish’,23452)
;WITH UserMaster AS
( SELECT
*,ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY UserName) AS ColumnNumber
FROM @UserData
)
SELECT DISTINCT
t.UserName
,t1.Mobile AS website1,t2.Mobile AS website2,t3.Mobile AS website3
FROM @UserData t
LEFT OUTER JOIN UserMaster t1 ON t.UserName=t1.UserName AND t1.ColumnNumber=1
LEFT OUTER JOIN UserMaster t2 ON t.UserName=t2.UserName AND t2.ColumnNumber=2
LEFT OUTER JOIN UserMaster t3 ON t.UserName=t3.UserName AND t3.ColumnNumber=3 Order byt.userName desc
