Jason
Jason

Reputation: 247

SQL Server Split one column into multiple columns

I'm trying to split one column of data into multiple (between 1 and 5 where necessary) based on the data in other columns being the same.
This is an example of my data after a query:

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

This is based on the fact that the same person at the same address can have multiple accounts, up to 5. What I need my results to look like:

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

So I need the accountNumber column split into multiple columns based on the criteria that the name/address is the same. I would like to just make 5 new columns and any empty field be a NULL value. Can anyone help?

Upvotes: 3

Views: 4606

Answers (1)

roman
roman

Reputation: 117337

you can use simple pivot:

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;

or you may pivot by hand:

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

Upvotes: 4

Related Questions