CODEWITHSUNDEEP
mysqlsqlselect
Philemon
Philemon

Reputation: 117

Joining two rows using MySQL statement

I'm looking for a SQL statement that could join 2 rows of the same table together.

Currently, my table looks like this:

LocationName | StationName | 12:00 - 13:00 | 13:00 - 14:00 | 15:00 - 16:00
T2           | Entrance    | Michael       | Michael       | Michael
T2           | Entrance    | Joyce         | Joyce         | Joyce

As you can see, there are 2 rows with the same LocationName and StationName. The only difference is that between the time slots, there were 2 people allocated inside it. My objective is to join these 2 rows into 1 using mysql statement.

I want it to look like this:

LocationName | StationName | 12:00 - 13:00 | 13:00 - 14:00 | 15:00 - 16:00
T2           | Entrance    | Michael Joyce | Michael Joyce | Michael Joyce

As you can see, 2 different names falling in the same column with only 1 row. This is what I want it to be. Any ideas?

Upvotes: 1

Views: 73

Answers (1)

John Woo
John Woo

Reputation: 263713

Use GROUP_CONCAT for this

SELECT LocationName, 
       StationName , 
       GROUP_CONCAT(`12:00 - 13:00` SEPARATOR ' ') `12:00 - 13:00`, 
       GROUP_CONCAT(`13:00 - 14:00` SEPARATOR ' ') `13:00 - 14:00`, 
       GROUP_CONCAT(`15:00 - 16:00` SEPARATOR ' ') `15:00 - 16:00`
FROM tableName
GROUP BY  LocationName, 
          StationName

SQLFiddle Demo

Upvotes: 6

Related Questions