CODEWITHSUNDEEP
sqlsql-server
user2841280
user2841280

Reputation:

how to pivot multiple records

This is my table structure !

create table t(floor int,apt int)
insert into t values(1,1),(1,2),(1,4),(2,5),(2,6),(2,7)

I want to get like this!

floor   room1   room2   room3
1       1       2       4
2       5       6       7

Upvotes: 1

Views: 59

Answers (2)

XN16
XN16

Reputation: 5869

Use a PIVOT in this case. 

SELECT * FROM
(
    SELECT floor,
           apt,
           NumberedApt = 'room' + CAST(ROW_NUMBER() OVER 
               (PARTITION BY floor ORDER BY apt) AS NVARCHAR(100))               
    FROM   t   
) AS OrderApts
PIVOT (MAX(apt) FOR Numberedapt IN (room1, room2, room3)) AS PivotedApts

Here is and SQLFiddle of the above working.

If you are going to get many more 'room' columns then you might want to consider using a dynamic pivot, but they can be inefficient due to not having a query plan.

Upvotes: 1

vhadalgi
vhadalgi

Reputation: 7189

More on pivot here

1.you need to use Row_number() partition by floor to get row then pivot to get your requirement

select p.floor,p.[1] as room1,p.[2] as room2,p.[3] as room3 from
(
select floor,apt,row_number() over(partition by floor order by apt) as rn from #t) as t

pivot
( 
min(t.apt)
for t.rn in([1],[2],[3]) 
)as p;

See in Action

Upvotes: 0

Related Questions