How to Pivot a Table in MS SQL

Question

2.50/5 (2 votes)

See more:

SQL-Server-2005

, +

SQL

select Floorno,RoomNo from floor

First_Floor	101
First_Floor	102
Second_Floor	201
Second_Floor	202
Third_Floor	301
Third_Floor	302

**************************************

I want to show this table as

First_Floor   101   102
Second_Floor  201   202
Third_Floor   301   302

Please Write the Full Query.

Posted 8-Aug-13 1:20am

rranjansir

Updated 8-Aug-13 1:57am

Maciej Los

v2

Add a Solution

Comments

rranjansir 8-Aug-13 7:54am

Then how to solve it..

Joezer BH 8-Aug-13 8:05am

Provide some more details, what the output should be, what coloumns are you expecting on the "pivoted" table?

What if there are 4 rooms on Second_Floor, then you have a conflict in the coloumns...

Maciej Los 8-Aug-13 8:10am

See my answer ;)

Maciej Los 8-Aug-13 8:01am

It's pivot!

Joezer BH 8-Aug-13 8:03am

Care to explain how it is pivot?

Maciej Los 8-Aug-13 8:11am

What to explain? See my answer... ;)

rranjansir 8-Aug-13 8:28am

please send link....If u hv answer...

rranjansir 8-Aug-13 8:29am

I want it for dynamic

1 solution

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)

Maciej Los · Accepted Answer · 2013-08-08T02:07:00

Try it:
1) Static version:

SQL

DECLARE @rooms TABLE (Floorno VARCHAR(30),RoomNo INT)

INSERT INTO @rooms (Floorno ,RoomNo)
SELECT 'First_Floor', 101
UNION ALL SELECT 'First_Floor', 102
UNION ALL SELECT 'Second_Floor', 201
UNION ALL SELECT 'Second_Floor', 202
UNION ALL SELECT 'Third_Floor', 301
UNION ALL SELECT 'Third_Floor', 302

SELECT Floorno, [1], [2]
FROM (
	SELECT Floorno ,RoomNo, ROW_NUMBER() OVER (PARTITION BY Floorno ORDER BY Floorno, RoomNo) AS RoomID
	FROM @rooms
	) AS DT
PIVOT(MAX(RoomNo) FOR RoomID IN([1],[2])) AS PT

Result:

Floorno          1       2
----------------------------
First_Floor	101	102
Second_Floor	201	202
Third_Floor	301	302

2) Dynamic version

SQL

CREATE TABLE #rooms (Floorno VARCHAR(30),RoomNo INT)

INSERT INTO #rooms (Floorno ,RoomNo)
SELECT 'First_Floor', 101
UNION ALL SELECT 'First_Floor', 102
UNION ALL SELECT 'Second_Floor', 201
UNION ALL SELECT 'Second_Floor', 202
UNION ALL SELECT 'Third_Floor', 301
UNION ALL SELECT 'Third_Floor', 302
UNION ALL SELECT 'Third_Floor', 303

DECLARE @cols VARCHAR(300)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT '],[' + CONVERT(NVARCHAR(10),O.[RoomID])
					FROM (
						SELECT ROW_NUMBER() OVER (PARTITION BY Floorno ORDER BY Floorno, RoomNo) AS RoomID
						FROM #rooms
					) AS O
					ORDER BY '],[' + CONVERT(NVARCHAR(10),O.[RoomID])
			FOR XML PATH('')),1,2,'') + ']'


SET @dt = N'SELECT Floorno ,RoomNo, ROW_NUMBER() OVER (PARTITION BY Floorno ORDER BY Floorno, RoomNo) AS RoomID
		FROM #rooms'
SET @pt = N'SELECT Floorno, ' + @cols + ' ' +
	'FROM (' + @dt +  ') AS DT ' +
	'PIVOT(MAX(RoomNo) FOR RoomID IN(' + @cols + ')) AS PT'

EXEC(@pt)

DROP TABLE #rooms