SQL 将多行合并为多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15290114/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Combine multiple rows into one with multiple columns
提问by RubenHerman
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!
但我仍然得到 2 行……地址 1 与地址 2 切换。有谁知道怎么只能得到一个吗？谢谢！
回答by
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
回答by shola
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.
您可以根据数据选择内连接或左连接。
