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

Question

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.

Answer 1

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

Answer 2

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

Answer 3

0

See also other questions here with the keyword "pivot"

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

answered Nov 13 '09 at 06:04 AM

Kristen ♦
2.2k ● 6 ● 7 ● 10

(comments are locked)

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

Follow this question

Related Questions