我的查询结果中有多行：
例如，表“ Address”：
Street | Number | City ---------------------- A1 | A2 | A3 B1 | B2 | B3
我真正想要的是：
Address1_Street | Address1_Number | Address1_City | Address2_Street | Address2_Number | Address2_City ------------------------------------------------------------------------------------------------------ A1 | A2 | A3 | B1 | B2 | B3
谁知道我能做到这一点吗？
我现在已经设法解决了这一点（很抱歉使用其他列，上面的只是一个例子，但是我想您会明白的）：
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)
但是我仍然得到2行…其中Address1与Address2切换。谁知道只会得到一个吗？谢谢！
尝试：
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
