0

This question already has an answer here:

We have following Data

----------------------------
RollNo        |  Seat
----------------------------
A1234         |   GN
A1233         |   GN
A1235         |   GNF
A1238         |   GN
A1233         |   GNF
A1231         |   GN
A1232         |   GNP

How can we get data in following format. Using SQL Only

----------------------------------------
GN       |     GNF     |  GNP
----------------------------------------
A1234    |  A1235      | A1232         
A1233    |  A1233      | 
A1238    |             | 
A1231    |             | 

I am using MS SQL SERVER

marked as duplicate by James Anderson, RolandoMySQLDBA, Community Jun 15 '16 at 21:18

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

You want to use a PIVOT and ROW_NUMBER function in order to accomplish your objective. See the example below.

The Setup

DECLARE @Seats AS TABLE
    (
    RollNo VARCHAR(10) NOT NULL
    , Seat CHAR(3) NOT NULL
    );

INSERT INTO @Seats
        ( RollNo, Seat )
VALUES  ('A1234','GN')
        ,('A1233','GN')
        ,('A1235','GNF')
        ,('A1238','GN')
        ,('A1233','GNF')
        ,('A1231','GN')
        ,('A1232','GNP');

The Query

SELECT GN, GNF, GNP
FROM (
    SELECT RollNo
        , Seat
        , ROW_NUMBER() OVER (PARTITION BY Seat ORDER BY RollNo) AS GroupID
    FROM @Seats) AS T PIVOT (MAX(RollNo) FOR Seat IN (GN, GNF, GNP)
    ) AS pt

Not the answer you're looking for? Browse other questions tagged or ask your own question.