asked on

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

Nasir Razzaq

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

Leo Torres

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

HLRosenberger

ASKER

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.

HLRosenberger

ASKER

maybe doing this in a stored proc is easier.

Leo Torres

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

Leo Torres

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

HLRosenberger

ASKER

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.

ASKER CERTIFIED SOLUTION

Leo Torres

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HLRosenberger

ASKER

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

HLRosenberger

ASKER

thanks.

Leo Torres

sure post link here once its done I will try to get to it

Nasir Razzaq

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

Leo Torres

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.

Nasir Razzaq

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