We help IT Professionals succeed at work.

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
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2014

Commented:
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

Commented:
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

Author

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.

Author

Commented:
maybe doing this in a stored proc is easier.

Commented:
Whats unique about those 2 records. That you only want those.

Commented:
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

Author

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.
Commented:
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.

Author

Commented:
Agreed. thanks.  I have a proc question; I'll create another post.

Author

Commented:
thanks.

Commented:
sure post link here once its done I will try to get to it
Most Valuable Expert 2012
Top Expert 2014

Commented:
@Leo
Would your code work for any number of rooms?

Commented:
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.
Most Valuable Expert 2012
Top Expert 2014

Commented:
Ok. The reason i ask is that i had already provided code to pivot fixed number of columns.