I have a database table, where information about an user is stored in several rows.
user_id, date, information_about_user_on_date 1, 2011-11-01, information_1 1, 2011-11-02, information_2 1, 2011-11-03, information_3
This information I want to show in one single line without getting this information row by row. SQL has the nice functionality „GROUP BY“ but by using this normally I’m only getting one information.
SELECT * FROM user_information GROUP BY user_id;
Ergebnis: 1, 2011-11-01, information_1
This means, I will loose 2 information. But now MySQL has the useful fonction „GROUP_CONCAT“ created, which can really help in this case.
SELECT user_id, date, GROUP_CONCAT(information_about_user_on_date SEPARATOR '|') AS info FROM user_information;
The result of this query looks like the following example:
user_id, date, info 1, 2011-11-01, information_1|information_2|information_3
Now I have all needed information in one single line. If I also want to have all dates from that example, I can do the same for that column.