x

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
more ▼

asked Nov 13, 2009 at 04:48 PM in Default

BrianLegg gravatar image

BrianLegg
2 3 3 3

Create table statement looked a lot nicer when I pasted it. Sorry.
Nov 13, 2009 at 05:29 PM BrianLegg
format your code using
 and  tags.
Nov 14, 2009 at 01:29 AM Nathan Skerl
"format your code using
 and  tags" Done :)
Nov 14, 2009 at 04:08 PM Kristen ♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Nov 14, 2009 at 01:29 AM

Nathan Skerl gravatar image

Nathan Skerl
432 1 1 3

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 16, 2009 at 05:38 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x79

asked: Nov 13, 2009 at 04:48 PM

Seen: 6292 times

Last Updated: Nov 14, 2009 at 04:07 PM