Pivoting multiple rows into one row with multiple columns

Question

question

BrianLegg asked Nov 13, '09

Pivoting multiple rows into one row with multiple columns

This relates to "Query that combines columns from multiple rows..." but I couldn't figure out how to add all this to it.

Using the tools from the two pivot solutions from the other question on this site, I came up with this query below. I've made both pivot solutions work actually, but they both return the same results. And that is 5798 rows (the amount of phonenumber entries in that table and not the 1445 distinct users' accountIDs.

In the result of this query, we see AccountID, phone1, phone2, phone3, and phone4 are column headings and there is a row for every combination of accoundID and phonenumber. Row 1, for example has 1 phone number in column phone1. Row 2 and 3 are for the next accountID. row 2 has a phone1 entry and row 3 has a phone2 entry. I want all the phone entries on the same row with one accountID. What am I doing wrong? I did keep coming across problems with mixing data types, especially aggregating text and int.

                    
Create table t3 (AccountID uniqueidentifier, phonenumber varchar(255))                    
insert t3                     
Select accountID, phonenumber from phonenumbers                    
GO                    
select                    
     AccountID,                    
    (case when sno=1 then phonenumber else '0' end) as phone1,                    
    (case when sno=2 then phonenumber else '0' end) as phone2,                    
    (case when sno=3 then phonenumber else '0' end) as phone3,                    
    (case when sno=4 then phonenumber else '0' end) as phone4                    
from                    
(                    
    select row_number() over(partition by AccountID order by AccountID) as sno,                    
           *                     
    from t3                    
) t3                    
group by AccountID, t3.sno, t3.phonenumber                    
Drop table t3

sql-server-2005 pivot

3

10 |1200

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

BrianLegg · Nov 13, 2009 at 05:29 PM

Create table statement looked a lot nicer when I pasted it. Sorry.

0 ·

Nathan Skerl · Nov 14, 2009 at 01:29 AM

format your code using

 and  tags.

0 ·

Kristen ♦ · Nov 14, 2009 at 04:08 PM

"format your code using

 and  tags" Done :)

0 ·

Answer 1 · 2009-11-14T01:29:10Z

Nathan Skerl answered Nov 14, '09

You are close. Use max aggregate to get one row, grouped on Account:

            
            
declare @t3 table (AccountId uniqueidentifier, phonenumber varchar(255))             
insert into @t3            
    select '11111111-1111-1111-1111-111111111111', '111-111-1111' union            
    select '11111111-1111-1111-1111-111111111111', '111-222-2222' union            
    select '11111111-1111-1111-1111-111111111111', '111-333-3333' union            
    select '22222222-2222-2222-2222-222222222222', '222-111-1111' union            
    select '22222222-2222-2222-2222-222222222222', '222-222-2222' union            
    select '33333333-3333-3333-3333-333333333333', '333-111-1111'             
            
            
    select	AccountID,             
    		max(case when sno=1 then phonenumber else null end)as phone1,             
    		max(case when sno=2 then phonenumber else null end) as phone2,             
    		max(case when sno=3 then phonenumber else null end) as phone3,             
    		max(case when sno=4 then phonenumber else null end) as phone4             
    from (	select	row_number() over(partition by AccountID order by AccountID) as sno ,            
    				*             
    		from @t3) t3             
    group by AccountID

10 |1200

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

Answer 2 · 2009-11-16T05:38:12Z

Madhivanan answered Nov 16, '09

or you dont need else '0' part in your query

10 |1200

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

question