...

SQL, concatenate multiple rows of strings and remove duplicates

 

The data table in MSSQL database stores flight connections. ID field is the group of connected flights. ROUTE field is the flight connection; the value consists of a pair of connected flights concatenated by a short dash. LNO field is the serial order of each pair of connected flights.

ID

ROUTE

LNO

1

JFK-LAX

1

1

LAX-IAD

2

1

IAD-ORD

3

5

MIA-ATL

1

5

ATL-MIA

2

2

SEA-IAH

1

2

IAH-AUS

2

2

AUS-SEA

3

Task: List the complete sequence of connections for each flight group (still use the short dash to connect flights) and arrange the sequences by flight group. Below is the expected result:

ID

ROUTE

1

JFK-LAX-IAD-ORD

2

SEA-IAH-AUS-SEA

5

MIA-ATL-MIA

Write the following SPL code:

A

1

=sqlServer1.query("select * from data order by ID,LNO")

2

=A1.group(ID;~.(ROUTE.split("-")).conj().id@o().concat("-"):ROUTE)

A1: Run the simple SQL; and pay attention to the data order.

A2: Group rows by ID and handle each group (represented by ~) – Split ROUTE field in each group by the short dash (-), concatenate them, remove neighboring duplicate flights (without sorting), and then concatenate the result with the short dash.

Source:https://stackoverflow.com/questions/78429500/remove-repetitive-characters-across-each-other-that-linked-with-dash


 
 •  51 View  •