SQL Combine multiple rows into one with multiple columns

sql, sql-server, sql-server-2008-r2

Solution

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

Problem

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!

Original source