I'm trying to manipulate an SQL table from the database Vertica.
Each row of the table has a start and end XY coordinates (which draws a subpath). Each subpaths when drawn in a map joins together to form a path (denoted by the unique route name). That is why the EndX, End Y is the same as the StartX, StartY for each route.
As the mapping software can only read only X & Y columns only, hence I need to squeeze them into just two columns.
To spare the details (think I did not explain well enough), I have a sample illustration below, on how the table looks like, and what it should become.
Provided a better raw data below for anyone who can help out to do a sample SQL statement. Thank you in advance!
Appreciate that your problem appears to distill down to adding the
n + 1 ordered pair for each route. One approach is then to simply
UNION together every starting point with a second query which identifies the end point.
SELECT RouteName, Sequence, StartX, StartY FROM yourTable UNION ( SELECT t1.RouteName, t1.Sequence + 1, t1.EndX, t1.EndY FROM yourTable t1 LEFT JOIN yourTable t2 ON t1.EndX = t2.StartX AND t1.EndY = t2.StartY AND t1.RouteName = t2.RouteName WHERE t2.RouteName IS NULL )
I didn't order the result set, but the tricky part here is coming up with the query, and hopefully this has you covered.