Click here to Skip to main content
15,919,613 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have columns like
Floor           RoomNo  
 1st floor      101
 2nd floor      201
 3rd floor      301
 4th floor      401
 1st floor      102
 2nd floor      202 
 3rd floor      302
 4th floor      402
 1st floor      103
 2nd floor      203
 3rd floor      303
 4th floor      403
 1st floor      104
 2nd floor      204
 3rd floor      304
 4th floor      404

i need the answer of

1st floor       101  102  103  104
2nd floor       201  202  203  204
3rd floor       301  302  303  304
4th floor       401  402  403  404
Updated 29-Jan-16 0:55am
F-ES Sitecore 29-Jan-16 6:48am    
What's your question?
V Senthil Kumar 29-Jan-16 6:56am    
convert row to column

HI,please use Pivot in your query which will convert rows into columns. See the links below for your help.

Simple Way To Use Pivot In SQL Query[^]

Efficiently convert rows to columns in sql server - Stack Overflow[^]
Share this answer
That's fairly nasty - it's basically a GROUP and a PIVOT, without the GROUP clause...
But, you can do it, using the ROW_NUMBER:
   (SELECT Floor, RoomNo, ROW_NUMBER() OVER (PARTITION BY Floor ORDER BY RoomNo) rn FROM MyTable1) sr
PIVOT (MIN(RoomNo) FOR rn IN ([1], [2], [3], [4])) pvt
Share this answer
V Senthil Kumar 29-Jan-16 7:30am    
it's worked to me .. can u explain using dynamic query its better to me

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900