Information from several rows in spite of „GROUP BY“

Following scenario:

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.

Print Friendly, PDF & Email

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.