SQL Server 将一列拆分为多列

Question

我正在尝试根据其他列中的数据相同将一列数据拆分为多列(必要时在 1 到 5 之间)。
这是我查询后的数据示例:

FirstName    LastName    StreetName    StreetNumber    AccountNumber
John         Smith       Fake St       123             10
John         Smith       Fake St       123             20
John         Smith       Fake St       123             30
John         Smith       Fake St       123             40

这是基于同一地址的同一个人可以拥有多个帐户这一事实，最多 5 个。我需要我的结果如下所示:

FirstName    LastName    StreetName    StreetNumber    AccountNumber1    AccountNumber2    AccountNumber3    AccountNumber4    AccountNumber5
John         Smith       Fake St       123             10                20                30                40                NULL

所以我需要根据名称/地址相同的标准将 accountNumber 列拆分为多个列。我只想创建 5 个新列，任何空字段都是 NULL 值。谁能帮忙？

Answer 1

您可以使用简单的数据透视表:

with cte as (
    select
        *,
        'AccountNumber' + 
        cast(
          row_number()
          over(
              partition by [FirstName], [LastName], [StreetName], [StreetNumber]
              order by AccountNumber
          )
        as nvarchar(max)) as rn
    from Table1
)
select *
from cte
pivot (
    max(AccountNumber)
    for rn in (
        [AccountNumber1],
        [AccountNumber2],
        [AccountNumber3],
        [AccountNumber4],
        [AccountNumber5]
    )
) as p;

或者您可以手动旋转:

with cte as (
    select
        *,
        row_number()
        over(
            partition by [FirstName], [LastName], [StreetName], [StreetNumber]
            order by AccountNumber
        ) as rn
    from Table1
)
select
    [FirstName], [LastName], [StreetName], [StreetNumber],
    max(case when rn = 1 then AccountNumber end) as AccountNumber1,
    max(case when rn = 2 then AccountNumber end) as AccountNumber2,
    max(case when rn = 3 then AccountNumber end) as AccountNumber3,
    max(case when rn = 4 then AccountNumber end) as AccountNumber4,
    max(case when rn = 5 then AccountNumber end) as AccountNumber5
from cte
group by [FirstName], [LastName], [StreetName], [StreetNumber]

sql fiddle demo