Combining multiple rows using SQL

Question

Below table displays package transferred from origin to destination.

[![enter image description here][1]][1]

Explanation:

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

Why do you need double number for seqNo? I'd use a regular integers; 1, 2, 3 etc. — jarlh
– jarlh, Commented Nov 24, 2022 at 10:13
@jarlh Primary focus is on the ContryFrom , CountryTo and Remarks. . SeqNo with intergers 1,2,3,4,5 is also okay. — Honey Singh
– Honey Singh, Commented Nov 24, 2022 at 10:15
@jarlh I am trying using self join but since I'm not at all good in queries so facing issues. I'd appreciate your help. — Honey Singh
– Honey Singh, Commented Nov 24, 2022 at 10:24
Self join is not anough as you need one self join for every node on a multi-path route. What you are looking for is a recursive query. See, for example, stackoverflow.com/questions/20215744/… — Stefan Winkler
– Stefan Winkler, Commented Nov 24, 2022 at 10:58

logesh sankar · Accepted Answer · 2022-11-24 11:05:39Z

3

I hope the below answer is suitable for you.

less than <

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('Send ', f.Country ,' ', f.Send,' Reached ', t.Country,' ',t.Arrive) Remarks from IPhone f inner join IPhone t on f.seqNo < t.seqNo order by id;

Thanking you

answered Nov 24, 2022 at 11:05

logesh sankar

2482 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Honey Singh Over a year ago

Beautifully done. . Thank you so much Sirji. Apka bahut bahut dhanyawad.

Stefan Winkler Over a year ago

Note that this will work if you don't need the remarks part, because all information is available in the self join. If you need the "via" information from the Remarks column shown in the question, you need a recursive query.

Honey Singh Over a year ago

Yes, but the requirement is to populate the remarks only if there is VIA otherwise keep it blank. I think I need to use the recursive anyway.

Honey Singh Over a year ago

@StefanWinkler Can you please help me in the recursive section please.

Collectives™ on Stack Overflow

Combining multiple rows using SQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related