How to transform my existing table into this specific format using T-SQL?

I am using SQL Server 2014. I have a table T1 (extract shown below):

StayID  ProfileID   Name    ArrivalDate
---------------------------------------
100       200       John    2020-03-15
120       300       Peter   2020-03-10
152       300       Peter   2020-04-25
210       400       Allan   2020-05-12
225       600       Smith   2020-03-28
415       600       Smith   2020-04-15
511       600       Smith   2021-02-20
625       900       Derek   2020-06-14
...

I would like to have the following output using a T-SQL query:

StayID  ProfileID      Name     ArrivalDate    StayID2    ArrivalDate2    StayID3      ArrivalDate3
100       200          John     2020-03-15      NULL          NULL         NULL             NULL
120       300          Peter    2020-03-10      152       2020-04-25       NULL             NULL
210       400          Allan    2020-05-12      NULL          NULL         NULL             NULL
225       600          Smith    2020-03-28      415       2020-04-15       511           2021-02-20
625       900          Derek    2020-06-14      NULL          NULL         NULL             NULL

The logic here is that data related to a specific ProfileID is to be transformed into the wide format. The StayID column is always unique.

I have been looking into the pivot function but I am not sure if that is the right path to tackle this problem. Any help would be appreciated.

Answer

Assuming you know the maximum number of groups you want in the result set, then you can use conditional aggregation:

select profileid, name,
       max(case when seqnum = 1 then arrivaldate end) as arrivaldate1,
       max(case when seqnum = 1 then stayid end) as stayid1,
       max(case when seqnum = 2 then arrivaldate end) as arrivaldate2,
       max(case when seqnum = 2 then stayid end) as stayid2,
       max(case when seqnum = 3 then arrivaldate end) as arrivaldate3,
       max(case when seqnum = 3 then stayid end) as stayid3
from (select t.*,
             row_number() over (partition by profileid, name order by arrivaldate) as seqnum
      from t
     ) t
group by profileid, name;

This answers the question that you have asked here. If you do not know the maximum number of groups, then you can use calculate the value in advance and adjust the query or use dynamic SQL.