Pivot Using Multiple Items

[Elegabalus]

I've got a table that has multiple records for a given Person (i.e., each Person has many offices, and each office has a phone number). I want to be able to flatten the records into one row for each Person.

Sample data:

DECLARE @test TABLE(PersonID1 varchar(10), PersonID2 varchar(10), OfficePhone varchar(20), OfficeAddress varchar(20))
insert into @test values ('c10xx2', 'joe', '555-123-456', '#1 First Street')
insert into @test values ('c10xx2', 'joe', '555-123-789', '#2 Second Street')
insert into @test values ('c134swd3', 'bill', '555-123-453', '#4 Third Street')
insert into @test values ('c134swd3', 'bill', '555-123-634', '#5 Fourth Street')

I want to turn this into:

PersonID1   PersonID2    Phone1          Address1          Phone2          Address2
--------    ----------  ---------     --------------     -----------    ----------------
c10xx2        joe      555-123-456   #1 First Street    555-123-789    #2 Second Street
c134swd3      bill     555-123-453   #4 Third Street    555-123-634    #5 Fourth Street

I've got the following code that works in a stored procedure:

DECLARE @Phone TABLE(PersonID1 varchar(10), PersonID2 varchar(10), Phone1 varchar(20), Phone2 varchar(20));
with P(PersonID1, PersonID2, OfficePhone, rowNo)
as
(SELECT PersonID1, PersonID2, OfficePhone, row_number() over(partition by PersonID1, PersonID2 order by PersonID1 DESC) as rowNo FROM @test)

INSERT INTO @Phone(PersonID1, PersonID2, Phone1, Phone2)

SELECT PersonID1, PersonID2, X.[1] as Phone1, X.[2] as Phone2
FROM P
pivot
(min(OfficePhone) for rowNo in ([1], [2], [3])
) X

DECLARE @Address TABLE(PersonID1 varchar(10), PersonID2 varchar(10), Address1 varchar(20), Address2 varchar(20));
with P(PersonID1, PersonID2, OfficeAddress, rowNo)
as
(SELECT PersonID1, PersonID2, OfficeAddress, row_number() over(partition by PersonID1, PersonID2 order by PersonID1 DESC) as rowNo FROM @test)

INSERT INTO @Address(PersonID1, PersonID2, Address1, Address2)

SELECT PersonID1, PersonID2, X.[1] as Address1, X.[2] as Address2
FROM P
pivot
(min(OfficeAddress) for rowNo in ([1], [2], [3])
) X

select p.PersonID1, p.PersonID2, Phone1, Phone2, Address1, Address2 
from @Phone p
	inner join @Address a on p.PersonID1 = a.PersonID1 AND p.PersonID2 = a.PersonID2

The problem is that the table I'm actually working against has many many rows, so I would like to put this into an indexed view. However, Common Table Expressions are not allowed in views, so I've run into a bit of a problem.

Two things:
1) Is there a better way of doing the above, instead of creating all those temp tables, and hitting the main table twice?
2) Is there a way of doing the above that will allow it to be done in an indexed view?

Any help is appreciated.

 

[Elegabalus]

The indexed view is not so important, but does anyone know of a more efficient way of getting these results?

I can't think that hitting the table 2 times to get one result set is the optimal way of getting these results.

Any help is appreciated.