Help with Pivot/rotate query.

I have a home table, and a related bed table.  Each home can have a max of 6 beds.   So,I want to end up with a once record per home, with the info for each of 6 bed possible bed in the record.  here's sample data:

home table
home#   HomeName        
100          "Jones home"
101           "smith home"

bed table
fk_home              BedNumber     BedStatus
100                             12                     vacant
100                             20                       vacant
100                              30                     occupied
101                             11                      occupied
101                             21                      vacant

I want to return 2 records like this, with 6 columns for bednumber and bedstatus, with NULLS where that bed does not exist.

home#           HomeName             Bed1Number              Bed1Status        Bed2Number                Bed2Status           etc.......
100                     "Jones home"                  12                        vacant                          20                             vacant
101                     "smith home"                  11                        occupied                      21                            vacant
LVL 1
HLRosenbergerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CodeCruiserCommented:
Here you go (would require slight modification to fit your schema)


Select Name, Bed1, Bed2, Bed3, Bed4, Bed5, Bed6
From
( Select H.Name, B.BedNo, B.[Status] From dbo.Beds B Inner Join dbo.Homes H On H.ID = B.HomeID) P
Pivot
(Max([Status]) For BedNo In (Bed1, Bed2, Bed3, Bed4, Bed5, Bed6)) As pvt
0
Leo TorresSQL DeveloperCommented:
Try

--PCSQ-30\DATATEST
--Exec RAPS.[dbo].[ProvLookup] '400037281000'


IF OBJECT_ID('tempdb..#homeTable') IS NOT NULL
	DROP table #homeTable

IF OBJECT_ID('tempdb..#bedtable') IS NOT NULL
	DROP table #bedtable

Select * into #homeTable
from (
Select 100 home, 'Jones Home' HomeName
Union
Select 101 home, 'Smith Home' HomeName
) a


Select * into #bedtable
from (
Select 100 as fk_home, 12 BedNumber,'Vacant' BedStatus
Union
Select 100 as fk_home, 20 BedNumber,'Vacant' BedStatus
Union
Select 100 as fk_home, 30 BedNumber,'Occupied' BedStatus
Union
Select 101 as fk_home, 11 BedNumber,'Occupied' BedStatus
Union
Select 101 as fk_home, 21 BedNumber,'Vacant' BedStatus
) a


Select * from #bedtable
Select * from #homeTable


Select
	 Home
	,HomeName
	,Bed1Number
	,Bed1Status
	,Bed2Number
	,Bed2Status
	,Bed3Number
	,Bed3Status
From (
Select 
	i.home
	,i.HomeName
	,i.BedNumber
	,i.BedStatus
	,'Bed'+Convert(Varchar(2),rn)+'Number' as BedXNumber
	,'Bed'+Convert(Varchar(2),rn)+'Status' as BedXStaus

 from 
(
Select 
	ht.home
	,ht.HomeName
	,bt.BedNumber
	,bt.BedStatus
	,ROW_NUMBER() Over (Partition by ht.Home Order by ht.Home) rn
From #bedtable bt
	Join #homeTable ht
		on bt.fk_home = ht.home
	) i	
) j
Pivot(Max(BedNumber) for [BedXNumber] in ([Bed1Number],[Bed2Number],[Bed3Number]) ) pvt
Pivot(Max(BedStatus) for [BedXStaus] in ([Bed1Status],[Bed2Status],[Bed3Status]) ) pvt2

Open in new window


This code will have to be dynamic if rooms unknown
0
HLRosenbergerAuthor Commented:
leo -

when I run  that I get 5 total records; I only want 2 records, based on my sample data.  This seems the be the issue, as I have tried other ways as well.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

HLRosenbergerAuthor Commented:
maybe doing this in a stored proc is easier.
0
Leo TorresSQL DeveloperCommented:
Whats unique about those 2 records. That you only want those.
0
Leo TorresSQL DeveloperCommented:
try
IF OBJECT_ID('tempdb..#homeTable') IS NOT NULL
	DROP table #homeTable

IF OBJECT_ID('tempdb..#bedtable') IS NOT NULL
	DROP table #bedtable

Select * into #homeTable
from (
Select 100 home, 'Jones Home' HomeName
Union
Select 101 home, 'Smith Home' HomeName
) a


Select * into #bedtable
from (
Select 100 as fk_home, 12 BedNumber,'Vacant' BedStatus
Union
Select 100 as fk_home, 20 BedNumber,'Vacant' BedStatus
Union
Select 100 as fk_home, 30 BedNumber,'Occupied' BedStatus
Union
Select 101 as fk_home, 11 BedNumber,'Occupied' BedStatus
Union
Select 101 as fk_home, 21 BedNumber,'Vacant' BedStatus
) a


Select * from #bedtable
Select * from #homeTable


Select
	 Home
	,HomeName
	,Sum(Coalesce(Bed1Number,0)) Bed1Number
	,MAX(Coalesce(Bed1Status,'')) Bed1Status 
	,Sum(Coalesce(Bed2Number,0)) Bed2Number
	,MAX(Coalesce(Bed2Status,'')) Bed2Status
	,Sum(Coalesce(Bed3Number,0)) Bed3Number
	,MAX(Coalesce(Bed3Status,'')) Bed3Status
From (
Select 
	i.home
	,i.HomeName
	,i.BedNumber
	,i.BedStatus
	,'Bed'+Convert(Varchar(2),rn)+'Number' as BedXNumber
	,'Bed'+Convert(Varchar(2),rn)+'Status' as BedXStaus

 from 
(
Select 
	ht.home
	,ht.HomeName
	,bt.BedNumber
	,bt.BedStatus
	,ROW_NUMBER() Over (Partition by ht.Home Order by ht.Home) rn
From #bedtable bt
	Join #homeTable ht
		on bt.fk_home = ht.home
	) i	
) j
Pivot(Max(BedNumber) for [BedXNumber] in ([Bed1Number],[Bed2Number],[Bed3Number]) ) pvt
Pivot(Max(BedStatus) for [BedXStaus] in ([Bed1Status],[Bed2Status],[Bed3Status]) ) pvt2
Group by 
	 Home
	,HomeName

Open in new window

0
HLRosenbergerAuthor Commented:
nothing is unique.  It's just sample data, for showing an example.   there can be hundreds of homes.

I working on a stored proc;  I have more experience using stored procs.
0
Leo TorresSQL DeveloperCommented:
yes this will have to be a stored Proc but it would have to be dynamic. It my be very complex I started to do one but ran out of time never got around to it. you have to build this pivot as a string then execute it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HLRosenbergerAuthor Commented:
Agreed. thanks.  I have a proc question; I'll create another post.
0
HLRosenbergerAuthor Commented:
thanks.
0
Leo TorresSQL DeveloperCommented:
sure post link here once its done I will try to get to it
0
CodeCruiserCommented:
@Leo
Would your code work for any number of rooms?
0
Leo TorresSQL DeveloperCommented:
I never got around to finishing the code. but it did get very Complex because you have so many unknowns and you have to create a lot of iterations to identify columns. I eventually just didnt think it was worth the time. No one has asked me to pivot data in 8 years of my SQL. Now a days its to easy to kust dump in Excel and create Pivots. IF I need to create 1 or 2 pivots I just write them.
0
CodeCruiserCommented:
Ok. The reason i ask is that i had already provided code to pivot fixed number of columns.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.