SQL 将多行合并为多列 [英] SQL Combine multiple rows into one with multiple columns

问题描述

我的查询结果中有多行:

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

例如表地址":

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

有谁知道我是如何做到这一点的?

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)

但我仍然得到 2 行...其中 Address1 与 Address2 切换.有谁知道怎么只能得到一个吗?谢谢！

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

推荐答案

尝试:

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

这篇关于SQL 将多行合并为多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！