I have a query that returns 4 lines per ID. I would like to be able to merge these into one single row in a new table, based on the ID and Date.

Can anyone help? I've researched and seem can't find any examples.

Simplified version of how the data looks at the moment:

view image

There's no more fields to identify if it's in or out. This is a log type database usually 4 entries a day per user id.


1 Answer 1


try this :

select t.user_id,t.time_log,substring_index(t.tstring,'#',1) am_in,substring_index(substring_index(t.tstring,'#',2),'#',-1) am_out,
substring_index(substring_index(t.tstring,'#',-2),'#',1) pm_in,substring_index(t.tstring,'#',-1) pm_out 
  (select h.user_id,h.time_log,group_concat(h.stime order by h.stime separator '#') tstring
  from user_hardware h
  group by h.user_id,h.time_log
  ) t;

But ensure that same stime will not occur twice for same user_id and time_log i.e. user_id,time_log,stime should be unique key.

Not the answer you're looking for? Browse other questions tagged or ask your own question.