-1

I have a table as follow:

staff_id | schedule
-------- | --------
1001     | 2020-01-02, 2020-01-09, 2020-01-16
1002     | 2020-01-05, 2020-01-12, 2020-01-19

I am expecting this table to look like following. What should be the query?

staff_id | schedule
-------- | --------
1001     | 2020-01-02
1001     | 2020-01-09
1001     | 2020-01-16
1002     | 2020-01-05
1002     | 2020-01-12
1002     | 2020-01-19
GMB
  • 216,147
  • 25
  • 84
  • 135
ray
  • 4,210
  • 8
  • 35
  • 46

2 Answers2

1

You can use multiset and hierarchy query to split your comaa separated data into rows using following:

select
  t.staff_id, 
  trim(regexp_substr(t.schedule, '[^,]+', 1, levels.column_value))  as schedule
from 
  Your_table t,
  table(cast(multiset(select level from dual 
                       connect by level <= regexp_count(t.schedule, ',') + 1) 
             as sys.OdciNumberList)) levels
order by staff_id, schedule;
Popeye
  • 35,427
  • 4
  • 10
  • 31
0

I like to use JSON functions for this - available starting Oracle 12.1:

select staff_id, x.schedule
from mytable t
cross apply json_table(
    '["' || replace(t.schedule, ', ', '", "') || '"]',
    '$[*]' columns (schedule varchar2(20) path '$')
) x

The trick is to turn the CSV string to a valid JSON array. Basically, this:

'2020-01-02, 2020-01-09, 2020-01-16'

Becomes:

'["2020-01-02", "2020-01-09", "2020-01-16"]'

You can then use json_table() to unnest the JSON array.

You might want to convert the resulting strings to dates - if so, use to_date() in the select clause:

select staff_id, to_date(x.schedule, 'yyyy-mm-dd') as schedule
from mytable t
cross apply json_table(
    '["' || replace(t.schedule, ', ', '", "') || '"]',
    '$[*]' columns (schedule varchar2(20) path '$')
) x
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    That's a cool trick as well! I never thought of converting it to a JSON array, but I like it! I'll try it out if I encounter this in the future. – EJ Egyed Dec 07 '20 at 14:27
  • This will work until it won't. Suppose, for example, that the strings representing dates include boilerplate text in double-quotes, as in `'2020-01-15"T"23:30:00'`. After you fix this (resulting in even uglier code), think of other ways in which this can go wrong - there are several more. Not a good approach... –  Dec 07 '20 at 19:15