Rows to Columns 1

[Elegabalus]

Bit of a stupid question, really, but most of the examples I've found through searching don't really do what I need.

I have two tables that are joined (person and person details).

When I pass in a person ID to get all the info on that person, multiple records are returned. I want to flatten the recordset out to one row. For instance:

Person ID      Person Phone
---------      -----------
1              555-123-1234
1              555-123-1235
1              555-123-1236

I would like this to be:

Person ID  Person Phone1   Person Phone2    Person Phone3
---------  ------------    -------------    -------------
1          555-123-1234     555-123-1235    555-123-1236

The columns are NOT dynamic...they have to be there no matter what (so, if there are only two rows returned in the original query, the Person Phone3 column should be null).

I've tried using Case statements, but I can't quite seem to get the logic of it figured out.

Any help would be appreciated.

 

[vongrunt]

SQL2000 or 2005?

How many max. phone numbers can one person have?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[Elegabalus]

SQL Server 2005

Max is 3 phone numbers.

 

[vongrunt]

> SQL Server 2005

Then... use ROW_NUMBER() to enumerate phone numbers for each PersonID and PIVOT() to denormalize results.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[vongrunt]

To illustrate:

create table #blah (PersonID int, Phone varchar(20))
insert into #blah values (1, '555 123-456')
insert into #blah values (1, '555 123-567')
insert into #blah values (1, '555 123-678')
insert into #blah values (2, '666 123-456')
insert into #blah values (2, '666 123-567')
insert into #blah values (3, '777 123-456')

with P( PersonID, Phone, rowNo )
as
(	select PersonID, Phone, row_number() over(partition by PersonID order by Phone) as rowNo
	from #blah
)
select PersonID, X.[1] as [Phone 1], X.[2] as [Phone 2], X.[3] as [Phone 3] 
from P
pivot
(	min(Phone)
	for rowNo in ([1], [2], [3])
) X
order by PersonID

Plz. note this puts phone with smallest ASCII value per personID in first column, next one in second column etc. Wanna different order, we need some more columns in sample data to explicitely determine that sort.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[oshlonger]

This is exactly what I am trying to do but I'm running on SQL Server 2000 and can have a max of 4 columns returned.

I don't think the ROW_COUNT() or PIVOT() functions are available.

Any thoughts?

 

[vongrunt]

Ranking can be simulated with insert into temp identity table or theta join, pivot with aggregate query. Wanna do it?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[oshlonger]

Yes, I've been looking through these forums for a couple of days. I need to get the data the way Elegabalus described with his second bit of code.

 

[vongrunt]

Actually that isn't so complex... use same sample temp table as above:

select PersonID,
	max(case when rowno = 1 then Phone end) as [Phone 1],
	max(case when rowno = 2 then Phone end) as [Phone 2],
	max(case when rowno = 3 then Phone end) as [Phone 3],
	max(case when rowno = 4 then Phone end) as [Phone 4]
from
(	select A.PersonID, A.Phone, count(*) as rowno
	from #blah A
	inner join #blah B on A.PersonID = B.PersonID and A.Phone >= B.Phone
	group by A.PersonID, A.Phone
) X
group by PersonID

Index on PersonID is recommended.

If ANSI warnings are not acceptable - turn 'em off temporarily If turning off is not acceptable we'll do some tweaks with ISNULL() function.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[oshlonger]

This seems to work great! Thanks. Let me apply it to my "real world" example and see what problems I have with that.

 

[Elegabalus]

Bit of new development that I'm having trouble with.

I need something similar to the above, but using multiple pivot items.

i.e.:

DECLARE @test TABLE(id varchar(10), name 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')

Should turn into:

ID          Name     OfficePhone1  OfficeAddress1     OfficePhone2   OfficeAddress2
--------    -------  ---------     --------------     -----------    ----------------
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

The trouble that I'm running into is with there being a phone and an address that are being flattened, instead of a single value.

Any help is greatly appreciated.

 

[Elegabalus]

For anyone that's interested, the only solution I found was to use the regular pivot syntax for each item, and then join the resulting tables together at the end to get the correct values.

i.e., Create a pivot table with the phone numbers, and insert the results into a temp table. Then create a pivot table with the addresses, using the same sort criteria, and insert the results into a different temp table.

Then, inner join the two temp tables on the ID, selecting the phone1, phone2, address1, and address2 fields.

Little bit clumsy, but it works.