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

  • Are there any other fields or flags to identify if the time was an "in" or "out". What happens if they log in/out a third time? Is this a timekeeping system? – Sir Swears-a-lot Aug 7 '16 at 6:28
  • Hi! 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. – Ken Dominguez Aug 7 '16 at 6:44

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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