Rich Rich - 4 months ago 7
SQL Question

merge two rows into one adding unique data

I have two rows for every order in a MYSQL database, they both have all the same information aside from:

pickup-location-id

I was wondering if there is a way to merge the rows into one while concatenating the

pickup-location-id for EX row1

pickup-location-id2 for EX row2

Into ONE ROW with the order-id used for both.

This is the closest I got....

SELECT *
FROM `t_order-transportation`
INNER JOIN (SELECT `pickup-location` AS `pickup-location-2`,
`order-id`
FROM `t_order-transportation`
GROUP BY `pickup-location`
HAVING COUNT(id) > 1) dup
ON `t_order-transportation`.`order-id` = dup.`order-id`


this is doing almost what I want, but it seems to be displaying 4 rows, 2 for the originals, and 2 for the new rows with the concationation both ways.

Sample of select return || RED IS ORIGINAL ROWS

MYSQL FIDDLE: http://sqlfiddle.com/#!9/4b441/1

Answer

I suggest a simple Inner Join

SELECT a.*, b.*
FROM `t_order-transportation` as a
INNER JOIN `t_order-transportation` as b 
              on ( a.`order-id` = b.`order-id` 
                 and a.`pickup-datetime` < b.`pickup-datetime`)

for the location you can

SELECT a.*, b.`pickup-location`
FROM `t_order-transportation` as a
INNER JOIN `t_order-transportation` as b 
              on (a.`order-id` = b.`order-id`
               and a.`pickup-datetime` < b.`pickup-datetime`)