Return 2 Separate Columns as 1 Column from MYSQL Query

Question

I am trying to handle as much as possible inside the query since this is the fastest way of listing things in my current project.

Here's what I'm trying to do.

I have a table with stations:

id      | station_call    | station_band
--------+-----------------+-------------
1       | WABC            | FM
2       | WXYZ            | AM

Now normally, upon getting the resutls, it would be easy to just join the two with PHP to get the full station name

$row["station_call"] . "-" . $row["station_band"];

would result in WACB-FM and WXYZ-AM

Is there a way I can get this joining of the two inside the query?

Basically returning a new row, something like station_name and have the name already formated as WACB-FM

Bonus:

This probably makes it a bit harder, my query is also getting these results inside of a JOIN statement and processed as a GROUP_CONCAT()

Right now, I have two get separate GROUP_CONCATS() to return as two separate columns in each row resulting in "WABC, WXYZ" and "FM, AM" and having to explode the strings and join them based on index

Basically, I need it to be returned as a series of station names separated as a comma.

So when I get the final row, I'm trying to just reference $row["stations"] and get "WABC-FM, WXYZ-AM"

@RowlandShaw You are correct. I was already using GROUP_CONCAT() I didn't know you can use CONCAT() inside another similar function — eeetee
– eeetee, Commented Feb 10, 2017 at 16:06

Oliver · Accepted Answer · 2017-02-10 16:07:53Z

6

SELECT concat(station_call, '-', station_band) AS station_info FROM stations;

You need the mysql-concat function.

The Solution within a group_concat-functions (Bonus-Part of question) looks like this:

SELECT GROUP_CONCAT(CONCAT(s.radiostation_call, '-', s.radiostation_band) SEPARATOR '|') AS station_info FROM stations GROUP BY ID

edited Feb 10, 2017 at 16:07

answered Feb 10, 2017 at 15:50

Oliver

8736 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eeetee Over a year ago

This worked, thanks. How in the world did I know about group_concat() and not about concat(), I had to use both in my example (see bonus): GROUP_CONCAT(CONCAT(s.radiostation_call, '-', s.radiostation_band) SEPARATOR '|')

Oliver Over a year ago

@eeetee For that stackoverflow is there. In 30 years of Computer Sience, I never met a developer who knew everything. I will add your final solution to my answer to be complete for others who read this.

Collectives™ on Stack Overflow

Return 2 Separate Columns as 1 Column from MYSQL Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related