如何添加逗号分隔的备注列？ 选择查询混乱

Question

I have 3 pairs of in/out employee records existing code.. but from the my table I need the remarks on each and every input be seen in one cell with comma separated.

Please see my fiddle.. http://sqlfiddle.com/#!2/afe72/1/0

My table will be like this..

create table overallrec(
    td_id int auto_increment primary key,
    emp_id int,  
    status varchar(3),
    timestamp datetime,
    remarks varchar(255)
);

insert into overallrec(emp_id,status,timestamp) values  
 ( 35, 'in' , '2013-12-19 10:15:09', 'late'),
 ( 35, 'out', '2013-12-19 12:00:23', 'example'),
 ( 35, 'in' , '2013-12-19 13:00:23'),
 ( 35, 'out', '2013-12-19 16:01:47'),
 ( 35, 'in' , '2013-12-19 18:01:17'),
 ( 35, 'out', '2013-12-19 22:01:07'),
 ( 35, 'in' , '2013-12-20 10:00:12'),
 ( 36, 'in' , '2013-12-18 10:15:09'),
 ( 36, 'out', '2013-12-18 12:00:23'),
 ( 37, 'in' , '2013-12-17 10:15:09'),
 ( 37, 'out', '2013-12-17 12:00:23'),
 ( 37, 'in' , '2013-12-17 13:00:23');

I want it to have a remarks at the end of each row with a comma separated strings. like this(reference 1st row from my fiddle):

35  in  December, 19 2013 10:15:09+0000
out December, 19 2013 12:00:23+0000
in  December, 19 2013 13:00:23+0000
out December, 19 2013 16:01:47+0000
in  December, 19 2013 18:01:17+0000
out December, 19 2013 22:01:07+0000      late, example

Please help me.. I'm hoping for an answer as a Christmas gift.. :/

Answer 1

GROUP_CONCAT can probably give you what you need.

Try this query to give you one resultset row per day per employee.

SELECT emp_id,
       GROUP_CONCAT(CONCAT(status, ' ', timestamp)) AS log,
       GROUP_CONCAT(remarks) AS remarks
  FROM overallrec
 GROUP BY emp_id, DATE_FORMAT(timestamp,'%Y-%m-%d')
 ORDER BY DATE_FORMAT(timestamp,'%Y-%m-%d'), emp_id

I didn't format your timestamps the same way you did, but other than that I think this is close. Here's a fiddle. http://sqlfiddle.com/#!2/d7755/5/0