question

BrianLegg avatar image
BrianLegg asked

Query that combines columns from multiple rows from source to one row in results.

Say I have a table with three fields:

                    
PKID                    
UserID                    
PhoneNumber                    

There are three rows

                    
Row1: 1 15 123                      
Row2: 1 15 456                     
Row3: 1 15 789                    

You can see that I have three separate phone numbers for userID 15. I want to display that in query results in one row: UserID, PhoneNumber1, PhoneNumber2, PhoneNumber3.

I have a much more complex query that gives me correct results in a cartesian product. I'm trying to eliminate the cartesian product.

sql-server-2005pivot
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do you want the phoneNumbers in seperate columns or a single column as a comma separated value?
0 Likes 0 ·
Seperate columns would be preferred. -BJL
0 Likes 0 ·
Ok, then see the PIVOT solution below:
0 Likes 0 ·
How many telephone numbers could there be, maximum, for a single person? That might pose a challenge if the number of columns it creates is high
0 Likes 0 ·
one instance of 4 and many hundreds of 3,2, and 1. So, although there is no physical limit, practically, 5 is all I expect.
0 Likes 0 ·
Show more comments
TG avatar image
TG answered

Here are alternatives for single column csv as well as pivot to separate columns:

use tempdb            
go            
create table t (pkid int, userid int, phonenumber varchar(10))            
go            
insert t             
select 1, 1, '123' union all            
select 1, 1, '456' union all            
select 1, 1, '789'             
            
go            
create function dbo.fn_phoneCSV (@userid int)            
returns varchar(50)            
as            
begin            
       declare @out varchar(50)            
       select @out = coalesce(@out + ', ' + phoneNumber, phoneNumber)            
       from   t            
       where  userid = @userid            
       return @out            
end            
go            
            
select pkid, userid, dbo.fn_phoneCSV(userid) as phoneNumbers            
from   t            
group by pkid, userid            
            
------------------------------------            
--PIVOT for separate columns            
select p.pkid            
       ,p.userid            
       ,[1] as phoneNumber1            
       ,[2] as phoneNumber2            
       ,[3] as phoneNumber3            
from   (            
       select pkid            
              ,userid            
              ,phonenumber, row_Number() over (partition by userid order by phonenumber) as phSeq            
       from   t            
       ) d            
pivot (max(phoneNumber) for phSeq in ([1],[2],[3])) p            
            
go            
            
drop function dbo.fn_phoneCSV            
drop table t            
            
OUTPUT:            
pkid        userid      phoneNumbers            
----------- ----------- --------------------------------------------------            
1           1           123, 456, 789            
            
            
            
pkid        userid      phoneNumber1 phoneNumber2 phoneNumber3            
----------- ----------- ------------ ------------ ------------            
1           1           123          456          789            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Madhivanan avatar image
Madhivanan answered

or this

declare @t table (pkid int, userid int, phonenumber varchar(10))            
            
insert @t             
select 1, 1, '123' union all            
select 1, 1, '456' union all            
select 1, 1, '789'             
            
select             
    pkid,userid,            
    max(case when sno=1 then phonenumber end) as phno1,            
    max(case when sno=2 then phonenumber end) as phno2,            
    max(case when sno=3 then phonenumber end) as phno3            
from            
(            
    select row_number() over(order by userid) as sno ,* from @t            
) as t            
group by pkid,userid            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered

See also other questions here with the keyword "pivot"

http://ask.sqlteam.com/questions/tagged/pivot

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.