Pivot Table problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Pivot Table problem

Author

Topic

Kimi86
Yak Posting Veteran

79 Posts

Posted - 2012-06-24 : 05:14:03

Hi All,
I am tryig to Pivot a table but unable to do so. Please help

My Table looks like this
owner occupant type
Kate student
Kate Personal
Kate Old
Kate House Help
Mike Student
Mike Old
Ram Student
Ram Old
Ram Personal

I need a select statement like this:
Owner Occupant_1 Occupant_2 Occupant_3 Occupant_4 Occupant_5 Occupant_6
Kate Student Personal Old House Help NULL NULL
Mike Student Old NULL NULL NULL NULL
Ram Student Old Personal NULL NULL NULL

Order in which the occumants are put in the view does not matter
There can be maximum of 6 occupants only

Scripts to create the Table are below:
Create Table Occupant_Type
(owner varchar(100),
occupant_type varchar(100))

Insert into Occupant_Type Values ('Kate','student')
Insert into Occupant_Type Values ('Kate','Personal')
Insert into Occupant_Type Values ('Kate','Old')
Insert into Occupant_Type Values ('Kate','House Help')
Insert into Occupant_Type Values ('Mike','Student')
Insert into Occupant_Type Values ('Mike','Old')
Insert into Occupant_Type Values ('Ram','Student')
Insert into Occupant_Type Values ('Ram','Old')
Insert into Occupant_Type Values ('Ram','Personal')

I need to this usnga select statement. Is that possible??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-24 : 10:11:53

[code]
SELECT owner,
[1] AS Occupant_1,
[2] AS Occupant_2,
[3] AS Occupant_3,
[4] AS Occupant_4,
[5] AS Occupant_5,
[6] AS Occupant_6
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY owner ORDER BY (SELECT 1)) AS Rn,owner,[occupant type]
FROM table
)t
PIVOT(MAX([occupant type]) FOR Rn IN ([1],[2],[3],[4],[5],[6]))p

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kimi86
Yak Posting Veteran

79 Posts

Posted - 2012-06-25 : 04:59:02

[quote]Originally posted by visakh16


SELECT owner,
[1] AS Occupant_1,
[2] AS Occupant_2,
[3] AS Occupant_3,
[4] AS Occupant_4,
[5] AS Occupant_5,
[6] AS Occupant_6
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY owner	ORDER BY (SELECT 1)) AS Rn,owner,[occupant type]
FROM table
)t
PIVOT(MAX([occupant type]) FOR Rn IN ([1],[2],[3],[4],[5],[6]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks you.. That was just brilliant...!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources