CODEWITHSUNDEEP
sqlsql-server
vap0991
vap0991

Reputation: 113

How to I convert this column to row format ?

I can use Pivot but my table here has only 2 columns so I don't know how to go about with it. A class has maximum of 5 UserIDs so I want to have a ClassID and associated 5 user names. 

UserID  ClassID
RK980   5
LO567   5
YY667   5
RT223   5
LT987   3

What I need is : 

ClassID User1 User2 User3 User4 User5
5         RK980 LO567 YY667 RT223 NULL
3         LT987 NULL  NULL  NULL  NULL

Thank you !

Upvotes: 0

Views: 58

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can use row_number(). I would then tend to go for conditional aggregation rather than pivot:

select classid,
       max(case when seqnum = 1 then userid end) as user1,
       max(case when seqnum = 2 then userid end) as user2,
       max(case when seqnum = 3 then userid end) as user3,
       max(case when seqnum = 4 then userid end) as user4,
       max(case when seqnum = 5 then userid end) as user5
from (select t.*, row_number() over (partition by classid order by userid) as seqnum
      from t
     ) t
group by classid;

Upvotes: 1

Serkan Arslan
Serkan Arslan

Reputation: 13393

you can use pivot with row_number

DECLARE @MyTable TABLE (UserID  VARCHAR(10),  ClassID INT)
INSERT INTO @MyTable VALUES
('RK980',   5 ),
('LO567',   5 ),
('YY667',   5 ),
('RT223',   5 ),
('LT987',   3 )

SELECT ClassID, [1] User1, [2] User2, [3] User3, [4] User4, [5] User5  FROM 
    (SELECT * ,
        ROW_NUMBER() OVER(PARTITION BY ClassID ORDER BY UserID ) AS RN
        FROM @MyTable ) SRC
    PIVOT(MAX(UserID) FOR RN IN ([1], [2], [3], [4], [5])) PVT

Result:

ClassID     User1      User2      User3      User4      User5
----------- ---------- ---------- ---------- ---------- ----------
3           LT987      NULL       NULL       NULL       NULL
5           LO567      RK980      RT223      YY667      NULL

Upvotes: 0

Related Questions