使用左连接查询创建列

Question

Well, I have two tables in mysql like this:

TABLE 1 - users
-------------
id | username | user_data
1  | George   | user_data
2  | John     | user_data


TABLE 2 - services
-------------
id | id_user | service_name | service_data
1  | 1       | lunch        | whatever
2  | 1       | dinner       | …
3  | 2       | lunch        | …

Well, with this if I make a query with Left Join like this:

SELECT username, service_name
FROM users
LEFT JOIN services ON users.id = services.id_user
WHERE 1

I got this:

username | service
George   | lunch
George   | dinner
John     | lunch

But I need to print to manage data in excel a grid like this:

username | service_1 | service_2
George   | lunch     | dinner
John     | lunch

I have a lot of rows of users and services and it is not possible make a query asking for services for each user.

Any idea to make this with only one query or any trick in PHP?

Answer 1

You could use GROUP_CONCAT, but note that this doesn't return the service names in separate columns, but just one column instead. For example:

SELECT username, GROUP_CONCAT(COALESCE(services.service_name, 'NA')) AS user_services
FROM users
LEFT JOIN services
    ON users.id = services.id_user
GROUP BY users.id;

will return:

I've created this sqlfiddle:

SQLFiddle