-2

I have a table data similar to below format in oracle :

create table employee_punches (emp_id number(38), name varchar2(40), id number(38), emp_fullname varchar2(80), workdate date, wbt_name varchar2(100), wbt_id number(38), timecodes varchar2(4000));  

insert into employee_punches values (17665, '10048',    1815944,    'Tsirnikas, Adrienne, Marie',   '09-NOV-20',    '381',  10644,  'ON-07:00,OFF-17:30,ON-18:30,OFF-19:12,ON-19:30,OFF-20:00,ON-21:42,OFF-23:00');  
insert into employee_punches values (18630, '30953',    1872069,    'Albert, Katherine, A',         '09-NOV-20',    '1',    10146,  'ON-05:00,OFF-14:30');  
insert into employee_punches values (18960, '37321',    1872482,    'Achimovich, Ashly',            '09-NOV-20',    '725',  10978,  'ON-15:00,OFF-23:00');  
insert into employee_punches values (19081, '39007',    1872713,    'Adams, Patty',                 '09-NOV-20',    '577',  10832,  'ON-07:00,OFF-15:30');  
insert into employee_punches values (19361, '42029',    1873203,    'Albert, Patricia',             '09-NOV-20',    '1',    10146,  'ON-10:00,OFF-22:00');

The above table needs to be queried and shown as below table. I need timecodes column be split into IN and and its corresponding OUT punches combination into two different columns as shown below. If there is no corresponding IN or OUT punch we need to leave that as NULL in the output under IN or OUT column accordingly.

Can you please suggest sample Oracle SQL query to convert the above table data into below format? I was trying to use regex_substr function but didn't work properly.

EMP_ID NAME ID EMP_FULLNAME WORKDATE WBT_NAME WBT_ID IN OUT
17665 10048 1815944 Tsirnikas, Adrienne, Marie 9-Nov-20 381 10644 7:00 17:30
17665 10048 1815944 Tsirnikas, Adrienne, Marie 9-Nov-20 381 10644 18:30 19:12
17665 10048 1815944 Tsirnikas, Adrienne, Marie 9-Nov-20 381 10644 19:30 20:00
17665 10048 1815944 Tsirnikas, Adrienne, Marie 9-Nov-20 381 10644 21:42 23:00
18630 30953 1872069 Albert, Katherine, A 9-Nov-20 1 10146 5:00 14:30
18960 37321 1872482 Achimovich, Ashly 9-Nov-20 725 10978 15:00 23:00
19081 39007 1872713 Adams, Patty 9-Nov-20 577 10832 7:00 15:30
19361 42029 1873203 Albert, Patricia 9-Nov-20 1 10146 10:00 22:00
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
vchitta
  • 2,043
  • 9
  • 28
  • 37
  • In my question above i am trying to tokenize `timecodes` column by comma and send ON tokens into IN column and immediate next OFF token into OUT column for that IN token. – vchitta Jan 07 '21 at 16:08
  • look at regular expressions to parse – OldProgrammer Jan 07 '21 at 16:17
  • Please do not ask [duplicate questions](https://stackoverflow.com/q/65613117/1509264) just because the first was closed. – MT0 Jan 07 '21 at 21:07

1 Answers1

1

This is a variant of the common problem "how do I split a comma-separated list into rows". You might want to review that question for my answer to make sense. Assuming that your data is clean, you can do something like this:

select emp_id, name, id, emp_fullname, workdate, wbt_name, wbt_id, 
    substr(regexp_substr(timecodes, '[^,]+', 1, level*2-1),4) as punch_in, 
    substr(regexp_substr(timecodes, '[^,]+', 1, level*2),5) as punch_out
from employee_punches
connect by regexp_substr(timecodes, '[^,]+', 1, level*2-1) is not null
      and PRIOR id = id 
      and PRIOR SYS_GUID() is not null  
   order by id, level;

You didn't specify what your primary key is, and this solution requires one, so I assumed that id will work. If you're using something different, you might need to change that in the connect by.

kfinity
  • 8,581
  • 1
  • 13
  • 20