Origin and destination using Recursive Query SQL-CodePudding

I am unable to get few of the table columns as it requires recursive query which I am not good at. So basically, if its a direct transfer, then remarks section is likely to be null. And if there is a halt in between origin and destination then I need to add the stations to my remarks column.

A to B -> nothing
B to C -> Via B
C -> D -> Via B,C

SQL query is:

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');

Tried

select  f.id,f.Country CountryFrom, t.Country CountryTo
, convert(varchar(4),f.seqNo)   '-'   convert(varchar(4),t.seqNo) seqNo
, f.Send, t.Arrive,concat('VIA ', f.Country ,', ', t.Country) Remarks from IPhone f inner join IPhone t on f.seqNo < t.seqNo order by id;

Gives

Requirement is the following. I tired looking into

CodePudding user response：

Recursive query is not required, This should work

CodePudding user response：

If like mentioned in the comments you use MS SQL you can construct recursive queries like so.

with base as (

   <some initial data select>

    UNION ALL

    select ...
    from base
    
)
select ...