尝试:
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