SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE  TABLE resu 
              (
                  header_id      numeric(10, 0) null,
                  appointment_id numeric(10, 0) null,
                  client_id      numeric(10, 0) null,
                  on_date        datetime       null,
                  on_time        char(5)        null,
                  room_id        numeric(10, 0) null
              )

INSERT INTO resu
(header_id,appointment_id, client_id ,on_date ,on_time,room_id )
VALUES
(654661,2784760,232834,   '2023-04-17 00:00:00.000','13:00',9),
(654661,2784761,232845,   '2023-04-17 00:00:00.000','13:00',9),
(654661,2784762,232836,   '2023-04-17 00:00:00.000','13:00',9),
(654661,2784763,232837,   '2023-04-18 00:00:00.000','13:00',9),
(654662,2784798,232888,   '2023-04-18 00:00:00.000','15:00',9),
(654662,2784799,232889,   '2023-04-18 00:00:00.000','15:00',9);
6 rows affected

select  header_id     ,
                 --- appointment_id ,
                  client_id     ,
  CONCAT('client_id',ROW_NUMBER() OVER ( PARTITION BY header_id ORDER BY appointment_id)) PT,
                  on_date      ,
                  on_time       ,
                  room_id      
  from resu
ORDER BY header_id,appointment_id 


header_id
client_id
PT
on_date
on_time
room_id


654661
232834
client_id1
2023-04-17 00:00:00.000
13:00
9

654661
232845
client_id2
2023-04-17 00:00:00.000
13:00
9

654661
232836
client_id3
2023-04-17 00:00:00.000
13:00
9

654661
232837
client_id4
2023-04-18 00:00:00.000
13:00
9

654662
232888
client_id1
2023-04-18 00:00:00.000
15:00
9

654662
232889
client_id2
2023-04-18 00:00:00.000
15:00
9


SELECT header_id,
MAX([client_id1]) client_id1, 
MAX([client_id2]) client_id2, 
MAX([client_id3] )client_id3 , 
MAX([client_id4] )client_id4 ,
MIN(on_date ) on_date,
MIN(on_time) on_time,
MIN(room_id)  room_id   
FROM (
select header_id,
       [client_id1], 
       [client_id2], 
       [client_id3], 
       [client_id4],
       on_date ,
        on_time,
        room_id    
from 
(
select  header_id,
        client_id ,
  CONCAT('client_id',ROW_NUMBER() OVER ( PARTITION BY header_id ORDER BY appointment_id,on_date, on_time, room_id)) PT,
         on_date ,
         on_time,
         room_id      
  from resu

) src
pivot
(
  MAX(client_id)
  for PT in ([client_id1], [client_id2], [client_id3], [client_id4])
) piv
)
  A
  GROUP BY header_id






header_id
client_id1
client_id2
client_id3
client_id4
on_date
on_time
room_id


654661
232834
232845
232836
232837
2023-04-17 00:00:00.000
13:00
9

654662
232888
232889
null
null
2023-04-18 00:00:00.000
15:00
9


Warning: Null value is eliminated by an aggregate or other SET operation.


select header_id,
     
       [client_id1], 
       [client_id2], 
       [client_id3], 
       [client_id4],
       on_date ,
        on_time,
        room_id    
from 
(
select  header_id,
        client_id ,
  CONCAT('client_id',ROW_NUMBER() OVER ( PARTITION BY header_id ORDER BY appointment_id,on_date, on_time, room_id)) PT,
         on_date ,
         on_time,
         room_id      
  from resu

) src
pivot
(
  MAX(client_id)
  for PT in ([client_id1], [client_id2], [client_id3], [client_id4])
) piv;



header_id
client_id1
client_id2
client_id3
client_id4
on_date
on_time
room_id


654661
232834
232845
232836
null
2023-04-17 00:00:00.000
13:00
9

654661
null
null
null
232837
2023-04-18 00:00:00.000
13:00
9

654662
232888
232889
null
null
2023-04-18 00:00:00.000
15:00
9

header_id	client_id	PT	on_date	on_time	room_id
654661	232834	client_id1	2023-04-17 00:00:00.000	13:00	9
654661	232845	client_id2	2023-04-17 00:00:00.000	13:00	9
654661	232836	client_id3	2023-04-17 00:00:00.000	13:00	9
654661	232837	client_id4	2023-04-18 00:00:00.000	13:00	9
654662	232888	client_id1	2023-04-18 00:00:00.000	15:00	9
654662	232889	client_id2	2023-04-18 00:00:00.000	15:00	9