group_concat

One great function I came across a few days ago is group_concat. Many times I've been asked to produce a query which pivots records in a table to take data which is presented in column format into a single row. In Oracle it can get pretty complicated either using a number of subqueries (which presents it's own problems) or analytical functions (which I'll admit I haven't used anywhere near enough).

Sitting on my desk is a timesheet, lets say the boss wants the reporting flexibility of adding in data one day at a time, fine we just create a table like so...

mysql> create table ts_data(emp_id int, week_no int, work_date date, hours numeric(4,2));
Query OK, 0 rows affected (0.11 sec)

I'll insert some data so we get the following data set.

mysql> select * from ts_data;
+--------+---------+------------+-------+
| emp_id | week_no | work_date  | hours |
+--------+---------+------------+-------+
|      1 |       1 | 2005-07-18 |  8.00 |
|      1 |       1 | 2005-07-19 |  8.00 |
|      1 |       1 | 2005-07-20 |  8.00 |
|      1 |       1 | 2005-07-21 |  8.00 |
|      1 |       1 | 2005-07-22 |  8.00 |
|      1 |       2 | 2005-07-23 |  8.00 |
|      1 |       2 | 2005-07-24 |  8.00 |
+--------+---------+------------+-------+
7 rows in set (0.00 sec)

So let's say the boss wants to see one row for each week with all of the hours worked side by side. That's where group_concat comes in. All we need to do is set up our grouping rules, in this case group by emp_id and week_no then specify the data we want to concatenate on a single row.

mysql> select emp_id, week_no, group_concat(work_date,' - ', hours) from ts_data group by emp_id, week_no;
+--------+---------+-------------------------------------------------------------------------------------------+
| emp_id | week_no | group_concat(work_date,' - ', hours)                                                      |
+--------+---------+-------------------------------------------------------------------------------------------+
|      1 |       1 | 2005-07-18 - 8.00,2005-07-19 - 8.00,2005-07-20 - 8.00,2005-07-21 - 8.00,2005-07-22 - 8.00 |
|      1 |       2 | 2005-07-23 - 8.00,2005-07-24 - 8.00                                                       |
+--------+---------+-------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)