Explode and implode strings in mysql query

Question

+------+-----------------+
|  id  |      name       +
+------+-----------------+
|  1   | David Gilmour   |
|  2   | Roger Waters    |
|  3   | Li Jen Ho       |
+------+-----------------+

Current format of names is firstName lastName and I want to change it to lastName firstName and I want to that in one database query.

The solution I've in my mind is to split names by space, reversing items and then imploding them again with space glue. But I don't know how to write a query for that.

I can simply do that in PHP by exploding names by space and putting them inside an array, reversing the array and then imploding the words by space glue but currently I don't have access to PHP.

Update 1: I found this similar question but I can't make it work. I hope I'm not asking a duplicate question.

Update 2: Names can have more than 2 parts. I assume only the first word is first name and the rest of name is last name.

Answer 1

I know I am late to the party but there is a better solution that can handle an unknown number of results.

GROUP_CONCAT

SELECT student_name,
  GROUP_CONCAT(DISTINCT test_score
               ORDER BY test_score DESC SEPARATOR ' ')
FROM student
GROUP BY student_name;