Building send and arrive times from multiple rows-CodePudding

The following data is about a person sending an IpHone 14 pro max to his wife. The Iphone 14 pro max has to travel from America to China, China to Argentina and finally from Argentina to Saudi Arabia since the girl is at Saudi Arabia watching FIFA.

CREATE TABLE IPhone (Id NCHAR(10), Country NCHAR(10), seqNo NCHAR(10), Send date, Arrive date)

INSERT INTO IPhone VALUES 
('1001','America','1', '2022-11-23 18:30:00.000',null),
('1002','China','2', '2022-11-24 08:18:00.000','2022-11-24 05:00:00'),
('1003','Argentina','3', '2022-11-25 18:30:00.000','2022-11-24 18:18:00.000'),
('1004','Saudi Arabia','4',null,'2022-11-25 20:30:00.000')

Explanation:

Sent from America on 2022-11-23 18:30:00.000 and reached on China at 2022-11-24 05:00:00
Sent from China on 2022-11-24 08:18:00.000 and
reached on Argentina on 2022-11-24 18:18:00.000
Sent from Argentina on 2022-11-25 18:30:00.000 and reached on Saudi Arabia on 2022-11-25 20:30:00.000

CodePudding user response：

You're looking for a self join to the next row as shown below. However I'm not convinced the desired results you show are correct, because you are using the arrive value of the next destination which seems odd.

CREATE TABLE IPhone (Id int, Country NVARCHAR(12), seqNo int, Send datetime2(0), Arrive datetime2(0));

INSERT INTO IPhone VALUES 
('1001','America','1', '2022-11-23 18:30:00.000',null),
('1002','China','2', '2022-11-24 08:18:00.000','2022-11-24 05:00:00'),
('1003','Argentina','3', '2022-11-25 18:30:00.000','2022-11-24 18:18:00.000'),
('1004','Saudi Arabia','4',null,'2022-11-25 20:30:00.000');

SELECT f.id, f.Country CountryFrom, t.Country CountryTo
    , convert(varchar(4),f.seqNo)   '-'   convert(varchar(4),t.seqNo) seqNo
    , f.Send, t.Arrive
FROM IPhone f
INNER JOIN IPhone t ON t.seqNo = f.seqNo   1
ORDER BY id;

Which returns:

id	CountryFrom	CountryTo	seqNo	Send	Arrive
1001	America	China	1-2	2022-11-23 18:30:00	2022-11-24 05:00:00
1002	China	Argentina	2-3	2022-11-24 08:18:00	2022-11-24 18:18:00
1003	Argentina	Saudi Arabia	3-4	2022-11-25 18:30:00	2022-11-25 20:30:00

Other way would be to link over seq_no

select 
    IPhone.Id,
    IPhone.Country,
    c.country,
    IPhone.Send_date,
    c.Arrive_date
from IPhone
left join IPhone c on IPhone.seqNo   1 = c.seqNo

Edit: As Dale mentioned you, your create table script is not ok so I've stored values as nvarchar values.

CodePudding user response：

hello maybe you can try this solution, https://www.c-sharpcorner.com/UploadFile/860b29/concatenate-multiple-rows-within-single-row-in-sql-server-20/ hope it can help you solve this problem.