Reputation: 247
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
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]
Upvotes: 4