Question 271353 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Merge two sql roes into one

ID StartTime StartLocation EndTime EndLocation
1 2023-10-15 10:00:00 A 2023-10-15 12:10:00 B
1 2023-10-15 13:00:00 B 2023-10-15 18:00:00 A

I want to merge these two rows into one so I get the first StartLocation and the last EndLocation. It should like this:

ID StartTime StartLocation EndTime EndLocation
1 2023-10-15 10:00:00 A 2023-10-15 18:00:00 A
Tags:

2

Answers


  1. 0

    Using ROW_NUMBER we can try:

    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY StartTime) rn1,
              ROW_NUMBER() OVER (ORDER BY EndTime DESC) rn2
    FROM yourTable
)

SELECT
    ID,
    MAX(CASE WHEN rn1 = 1 THEN StartTime END) AS StartTime,
    MAX(CASE WHEN rn1 = 1 THEN StartLocation END) AS StartLocation,
    MAX(CASE WHEN rn2 = 1 THEN EndTime END) AS EndTime,
    MAX(CASE WHEN rn2 = 1 THEN EndLocation END) AS EndLocation
FROM cte
GROUP BY ID
ORDER BY ID;

    screen capture from demo link below

    Demo

    Login or Signup to reply.
  2. 0 
    SELECT t1.id, 
       t1.StartTime, t1.StartLocation,
       t2.EndTime, t2.EndLocation
FROM table AS t1
JOIN table AS t2 ON t1.id = t2.id
                AND t1.EndLocation = t2.StartLocation
                AND t1.EndTime < t2.StartTime

    If the amount of rows may be more than 2 then you’d use recursive CTE and build complete rows chain.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search