CODEWITHSUNDEEP
phpmysql
David Bélanger
David Bélanger

Reputation: 7438

Create one row from others rows

I got a problem. I have data in a table that look like that :

route_id    route_short_name    route_long_name

AOUT1112E   12                  Direction Pont-Viau
AOUT1112O   12                  Direction Métro Cartier
JANV1212E   12                  Direction Pont-Viau
JANV1212O   12                  Direction Métro Cartier
JRAN1212E   12                  Direction Pont-Viau
JRAN1212O   12                  Direction Métro Cartier

I need the result to be like that :

12 - Direction Pont-Viau / Direction Métro Cartier

OR

12 - Direction Pont-Viau / Métro Cartier

I tought I may use two arrays but I'd like to do this in MySQL. Also, I got other routes in the table like route 13, 15, etc so it need to work in a SELECT ALL Anyone have any idea ?

Thanks a lot

Upvotes: 0

Views: 66

Answers (1)

RickN
RickN

Reputation: 13500

To combine two strings, use CONCAT(). To join the values in a group, use GROUP_CONCAT.

SELECT 
    *, 
    CONCAT(
        route_short_name,
        " - ",
        GROUP_CONCAT(route_long_name SEPARATOR ' / ')
    ) AS route_name
FROM 
    ...
GROUP BY
    route_short_name

It would return something like this:

SHORTNAME - LONGNAME1 / LONGNAME2 / LONGNAME3

The above would only return one row per route_short_name. To get them all, use a subquery:

SELECT *, (
    SELECT CONCAT(
        route_short_name, 
        " - ", 
        GROUP_CONCAT(route_long_name.b SEPARATOR " / "))
    FROM 
        your_table_name AS your_table_name_inner
    WHERE 
        your_table_name_inner.route_short_name = your_table_name_outer.short_name
    GROUP BY 
        your_table_name.route_short_name
) AS route_name
FROM
    your_table_name AS your_table_name_outer

The subquery is pretty much the same as the first example, except that it always returns a single row in the format SHORTNAME - LONGNAME1 / LONGNAME2 / etc. The outer query returns all records and for every record, the subquery is executed. It goes without saying that this is a heavier query than the first example.

On closer inspection, its seems that the route_short_name is actually quite irrelevant to the database structure, unlike what I assumed.

I've left the above answer, because it might help someone else.

If I understand correctly, you want to join two (not more, not less) records, where one has a route_id ending with an E and one with an O.

Assuming the following:

  • The ID is always 8 characters + an E or O.
  • There are always two records that belong together.

In that case:

SELECT 
    *, 
    CONCAT(a.route_short_name, ' - ', a.route_long_name, ' / ', b.route_long_name)
FROM
    your_table_name AS a
JOIN
    your_table_name AS b
ON
    LEFT(a.route_id, 8) = LEFT(b.route_id, 8)
AND
    a.route_id != b.route_id

This does the following:

  1. Return the short name, a dash, long name 1, slash, long name 2.
  2. Join based on the first 8 characters of the IDs (LEFT(str, n) returns the first n characters of a string).
  3. The IDs may not be an exact match (which prevents joining with itself).

This might be rather slow (due to the use of LEFT()), but MySQL might have some black magic that optimizes things and indexes might help as well. Benchmark it on your real table.

Upvotes: 1

Related Questions