SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Pivot Table problem		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kimi86
Yak Posting Veteran

54 Posts
Posted - 06/24/2012 :  05:14:03  Show Profile  Reply with Quote
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

India
47099 Posts
Posted - 06/24/2012 :  10:11:53  Show Profile  Reply with Quote 

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/
Go to Top of Page

Kimi86
Yak Posting Veteran

54 Posts
Posted - 06/25/2012 :  04:59:02  Show Profile  Reply with Quote
[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...!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000