How to combine multiple columns as one and format with custom strings?

Question

SELECT id, <X> AS name FROM `table`

Basically <X> is a combination of
lastname + ', ' + firstname

example would be

   id | name        |
   2  | Smith, Bob  |
   3  | Jones, Susy |

This is just an example, I don't really want to combine names so simple.

Answer 1

What about the CONCAT() function?

SELECT id, CONCAT(lastname, ', ', firstname) name FROM `table`;

If you are going to concatenate many fields, you could also consider the CONCAT_WS() function, where the first argument is the separator for the rest of the arguments, which is added between the strings to be concatenated:

SELECT id, 
       CONCAT_WS(',', field_1, field_2, field_3, field_4) list
FROM   `table`;

Answer 2

use concat like :

SELECT id, CONCAT(lastname, ' , ', firstname) AS name FROM `table`;