MySQL Tip – Concatenate Multiple Values Across Rows
The function group_concat in MySQL will let you combine the values from multiple rows together in an aggregate function.
It’s a little hard to picture, so here’s a hypothetical aggregate query from the Netflix prize dataset:
select movie_id, customer_id, max(date) as latest, group_concat(rating) as all_ratings, count(*) as total_ratings from ratings group by movie_id,customer_id having count(*)>1;
And we get back* **:
+----------+-------------+------------+-------------+---------------+ | MOVIE_ID | CUSTOMER_ID | LATEST | ALL_RATINGS | TOTAL_RATINGS | +----------+-------------+------------+-------------+---------------+ | 1 | 5 | 2007-12-02 | 3,2,4,5 | 4 | | 54 | 3 | 2007-12-04 | 1,3,1 | 3 | | 23 | 34 | 2007-12-07 | 5,5 | 2 | +----------+-------------+------------+-------------+---------------+
As you can see for each set of rows grouped by the same movie_id and customer_id, it took each individual rating value and concatenated them all together in the new aggregate row.
* My actual query returned 0 rows so I thought I’d make up a quick example to illustrate the point.
** I made that pretty table*** with this ASCII table generating utility. Yay!
*** Pretty table may not be pretty in RSS readers or Blackberries.