SQLselect query for different value in same column for same userid

Question

I have the following table in MySQL:

id      name       destination     rating     country
----------------------------------------------------
1       James      Barbados        5          WI
1       James      Antigua         6          WI
1       James      mumbai          3          WI
2       Declan     Trinidad        2          WI
2       Declan     Barbados        4          WI
2       Declan     Trinidad        3          WI

I want to write a select query which will fetch name and destination for example

name destination1 destination2 destination3
James mumbai       Antigua      Barbados  

Answer 1

You could do something like this

SELECT id, GROUP_CONCAT(destination DESC SEPARATOR ' ') FROM tablename GROUP BY id;

but, it is necessary to say, that the information is not well structured in your model, you should create at least 3 tables, something like

USER
id  name

DESTINATIONS
id  destination     rating     country

USERS_DESTINATIONS
id  iduser  iddestination