SQL Combine multiple rows into one with multiple columns

Question

I've got multiple rows in my result from my query:

for example, the table "Address":

Street | Number | City
----------------------
A1     | A2     | A3
B1     | B2     | B3

What I actually want is:

Address1_Street | Address1_Number | Address1_City | Address2_Street | Address2_Number | Address2_City
------------------------------------------------------------------------------------------------------
A1              | A2              | A3            | B1              | B2              | B3

Anyone who knows how I can achieve this?

I've managed to get to this point now (sorry for using other columns, the one above was an example, but I'll guess you'll get the point):

select distinct
    a.ID,
    a.Name, 
    ca1.NameLine1 as Address1_NameLine1, 
    ca2.NameLine1 as Address2_NameLine1
from 
    dbo.Accounts a, 
    dbo.Addresses ca1,
    dbo.Addresses ca2
where 
        (a.ID = ca1.AccountID AND a.ID = ca2.AccountID)
    AND (a.Name = 'TEST')
    AND (ca1.ID <> ca2.ID)

But I'm still getting 2 rows... where Address1 switches with Address2. Anyone who knows how to only get one? Thanks!

is there a key on Address which determines which rows should be mashed together, or do you simply want every row in the address table? — ninesided
– ninesided, Commented Mar 8, 2013 at 9:00
In many RDBMSs there is no way to do this for an arbitrary number of columns. — user1919238
– user1919238, Commented Mar 8, 2013 at 9:04

score 5 · Accepted Answer · 2013-03-08 10:50:37Z

5

Try:

select ID,
       max(Name) Name,
       max(case when rn=1 then NameLine1 end) Address1_NameLine1,
       max(case when rn=2 then NameLine1 end) Address1_NameLine2
from
(select a.ID,
        a.Name, 
        ca.NameLine1,
        rank() over (partition by a.ID order by ca.ID) rn
 from dbo.Accounts a 
 join dbo.Addresses ca on a.ID = ca.AccountID
 where a.Name = 'TEST') sq
group by ID

edited Mar 8, 2013 at 10:50

answered Mar 8, 2013 at 10:13

user359040

Sign up to request clarification or add additional context in comments.

3 Comments

RubenHerman Over a year ago

One last question... It's possible that the account ONLY has 1 Address, how should I check this? (so Address2_NameLine1 should return NULL)

user359040 Over a year ago

@RubenHerman: Actually, the previous version worked OK where there was only one address, but returned two rows where there was a second address - this new version should work OK for either one or two addresses per account.

RubenHerman Over a year ago

Thanks! You were a great help!

User1000547 · Accepted Answer · 2016-11-29 17:07:26Z

1

select address1.* ,address2.* from
Address address1 inner join on Address address2
on address1.userid=address2.userid

you can choose inner or left join based on data.

edited Nov 29, 2016 at 17:07

User1000547

4,3219 gold badges44 silver badges68 bronze badges

answered Mar 8, 2013 at 9:13

shola

7044 silver badges11 bronze badges

2 Comments

RubenHerman Over a year ago

And this only returns one row then?

shola Over a year ago

You can choose left join instead of inner join.

Collectives™ on Stack Overflow

SQL Combine multiple rows into one with multiple columns

2 Answers 2

3 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Linked

Related