DEV Community

Judy
Judy

Posted on

SQL, concatenate multiple rows of strings and remove duplicates #eg27

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.

Image description
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:

Image description
Write the following SPL code:

Image description
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
SPL open source address

Top comments (0)