I need help to split rows to column

Question

0.00/5 (No votes)

See more:

i have columns like

SQL

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

SQL

1st floor       101  102  103  104
2nd floor       201  202  203  204
3rd floor       301  302  303  304
4th floor       401  402  403  404

Posted 29-Jan-16 0:30am

V Senthil Kumar

Updated 29-Jan-16 0:55am

v2

Add a Solution

Comments

F-ES Sitecore 29-Jan-16 6:48am

What's your question?

V Senthil Kumar 29-Jan-16 6:56am

convert row to column

2 solutions

Solution 1

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[^]

Posted 29-Jan-16 1:20am

deepankarbhatnagar

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2016-01-29T01:25:00

Solution 2

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:

SQL

SELECT * FROM
   (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

Posted 29-Jan-16 1:25am

OriginalGriff

Comments

V Senthil Kumar 29-Jan-16 7:30am

it's worked to me .. can u explain using dynamic query its better to me