Источник - это ТРИ столбца, где третий столбец (ЧАСЫ) имеет тип VARCHAR и имеет значения CSV:
Monday, 10:30AM–12AM, Tuesday, 10:30AM–12AM, Wednesday, 10:30AM–12AM, Thursday, 10:30AM–12AM, Friday, 10:30AM–12AM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM
Желаемый вывод в 4 столбцах, где ЧАСЫ будут переведены в столбцы ДЕНЬ и РАСПИСАНИЕ.
Я БЫ | ПРОДАВЕЦ | ДЕНЬ | РАСПИСАНИЕ |
---|---|---|---|
1001 | азбука | Понедельник | 10:30–12:00 |
1001 | азбука | Вторник | 10:30–12:00 |
1001 | азбука | Среда | 10:30–12:00 |
1001 | азбука | Четверг | 10:30–12:00 |
1001 | азбука | Пятница | 10:30–12:00 |
1001 | азбука | Суббота | 10:30–12:00 |
1001 | азбука | Воскресенье | 10:30–12:00 |
Таким образом, 1 ID, ПОСТАВЩИК с 1 ЧАСОМ необходимо преобразовать в 7 строк.
Вот пример таблицы с образцами записей:
CREATE TABLE t1 (id NUMBER, VENDOR VARCHAR2, HOURS VARCHAR2);
INSERT INTO t1 (id, vendor, hours)
VALUES
(1001, 'ABC', 'Monday, 07:00AM–12AM, Tuesday, 10:30AM–12AM, Wednesday, 10:30AM–12AM, Thursday, 10:30AM–12AM, Friday, 10:30AM–12AM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM'),
(1002, 'DEF', 'Monday, 08:00AM–01PM, Tuesday, 08:00AM–01PM, Wednesday, 08:00AM–01PM, Thursday, 08:00AM–01PM, Friday, 08:00AM–01PM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–01PM'),
(1003, 'GHE', 'Monday, 09:00AM–02PM, Tuesday, 09:00AM–02PM, Wednesday, 09:00AM–02PM, Thursday, 09:00AM–02PM, Friday, 09:00AM–02PM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM'),
(1004, 'IJK', 'Monday, 10:00AM–03PM, Tuesday, 10:00AM–03PM, Wednesday, 10:00AM–03PM, Thursday, 10:00AM–03PM, Friday, 10:00AM–02PM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM'),
(1005, 'LMN', 'Monday, 10:30AM–04PM, Tuesday, 10:30AM–12AM, Wednesday, 10:30AM–12AM, Thursday, 10:30AM–12AM, Friday, 10:30AM–02PM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM');
WITH days_of_week AS (
SELECT * FROM (
SELECT column1, ROW_NUMBER() OVER (ORDER BY1) AS row_number
FROM VALUES
('Monday'), ('10:30AM–12AM'), ('Tuesday'), ('10:30AM–12AM'), ('Wednesday'), ('10:30AM–12AM'), ('Thursday'), ('10:30AM–12AM'), ('Friday'), ('10:30AM–12AM'), ('Saturday'), ('10:30AM–12AM'), ('Sunday'), ('10:30AM–12AM')
)
WHERE row_number % 2 = 1 ),
hours_open AS (
SELECT * FROM(
SELECT column1, ROW_NUMBER() OVER (ORDER BY1) AS row_number
FROM VALUES
('Monday'), ('10:30AM–12AM'), ('Tuesday'), ('10:30AM–12AM'), ('Wednesday'), ('10:30AM–12AM'), ('Thursday'), ('10:30AM–12AM'), ('Friday'), ('10:30AM–12AM'), ('Saturday'), ('10:30AM–12AM'), ('Sunday'), ('10:30AM–12AM')
)
WHERE row_number % 2 = 0)
SELECT d.column1 AS Day, h.column1 AS Schedule
FROM days_of_week d
INNER JOIN hours_open h
ON d.row_number = h.row_number-1;
Спасибо за ответ, но прошу прощения за неполную информацию. Значения в столбце будут динамическими. Я добавил образец таблицы и записи в описание сейчас.
CREATE TABLE DEV.EDW.t1 (id NUMBER, VENDOR VARCHAR2, HOURS VARCHAR2);
INSERT INTO DEV.EDW.t1 (id, vendor, hours)
VALUES
(1001, 'ABC', 'Monday, 07:00AM–12AM, Tuesday, 10:30AM–12AM, Wednesday, 10:30AM–12AM, Thursday, 10:30AM–12AM, Friday, 10:30AM–12AM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM'),
(1002, 'DEF', 'Monday, 08:00AM–01PM, Tuesday, 08:00AM–01PM, Wednesday, 08:00AM–01PM, Thursday, 08:00AM–01PM, Friday, 08:00AM–01PM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–01PM'),
(1003, 'GHE', 'Monday, 09:00AM–02PM, Tuesday, 09:00AM–02PM, Wednesday, 09:00AM–02PM, Thursday, 09:00AM–02PM, Friday, 09:00AM–02PM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM'),
(1004, 'IJK', 'Monday, 10:00AM–03PM, Tuesday, 10:00AM–03PM, Wednesday, 10:00AM–03PM, Thursday, 10:00AM–03PM, Friday, 10:00AM–02PM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM'),
(1005, 'LMN', 'Monday, 10:30AM–04PM, Tuesday, 10:30AM–12AM, Wednesday, 10:30AM–12AM, Thursday, 10:30AM–12AM, Friday, 10:30AM–02PM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM');
SELECT * FROM DEV.EDW.t1;
SELECT Id,Vendor,DAY,SCHEDULE FROM (
Select src.id,src.Vendor
,CASE WHEN Index%2=1 THEN VALUE END AS DAY
,LEAD(VALUE) OVER(PARTITION BY SEQ ORDER BY INDEX) AS SCHEDULE
From (SELECT ID,VENDOR,HOURS FROM DEV.EDW.t1 ) src, table(strtok_split_to_table(src.HOURS,','))
) out WHERE DAY IS NOT NULL
ORDER BY ID,Vendor
;
Спасибо за ответ, но прошу прощения за неполную информацию. Значения в столбце будут динамическими. Я добавил образец таблицы и записи в описание сейчас.
@LazyDeveloper Эй, я только что заменил ручной ввод табличным вводом и отлично работаю с динамическими значениями при условии, что шаблон (день, часы) остается прежним. Пожалуйста, проверьте. спасибо
Спасибо Аншул за помощь
Ах, забавно, настройте ввод так, чтобы у него была только одна запятая в день / час, затем разделите_в_таблицу, а затем разделите по настройке для быстрых побед, весело!
так что с вами отличные образцы данных превратились в CTE:
WITH cte_table_data as (
SELECT * FROM VALUES
(1001, 'ABC', 'Monday, 07:00AM–12AM, Tuesday, 10:30AM–12AM, Wednesday, 10:30AM–12AM, Thursday, 10:30AM–12AM, Friday, 10:30AM–12AM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM'),
(1002, 'DEF', 'Monday, 08:00AM–01PM, Tuesday, 08:00AM–01PM, Wednesday, 08:00AM–01PM, Thursday, 08:00AM–01PM, Friday, 08:00AM–01PM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–01PM'),
(1003, 'GHE', 'Monday, 09:00AM–02PM, Tuesday, 09:00AM–02PM, Wednesday, 09:00AM–02PM, Thursday, 09:00AM–02PM, Friday, 09:00AM–02PM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM'),
(1004, 'IJK', 'Monday, 10:00AM–03PM, Tuesday, 10:00AM–03PM, Wednesday, 10:00AM–03PM, Thursday, 10:00AM–03PM, Friday, 10:00AM–02PM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM'),
(1005, 'LMN', 'Monday, 10:30AM–04PM, Tuesday, 10:30AM–12AM, Wednesday, 10:30AM–12AM, Thursday, 10:30AM–12AM, Friday, 10:30AM–02PM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM')
t(id, vendor, hours)
)
и этот SQL
select
c.id,
c.vendor,
trim(split_part(v.value,'|',1)) as day,
trim(split_part(v.value,'|',2)) as SCHEDULE
from cte_table_data as c
,table(split_to_table(replace(c.hours,'y,','y|'), ',')) v
order by v.seq, v.index;
Я БЫ | ПРОДАВЕЦ | ДЕНЬ | РАСПИСАНИЕ |
---|---|---|---|
1001 | азбука | Понедельник | 07:00–12:00 |
1001 | азбука | Вторник | 10:30–12:00 |
1001 | азбука | Среда | 10:30–12:00 |
1001 | азбука | Четверг | 10:30–12:00 |
1001 | азбука | Пятница | 10:30–12:00 |
1001 | азбука | Суббота | 10:30–12:00 |
1001 | азбука | Воскресенье | 10:30–12:00 |
1002 | ДЭФ | Понедельник | 08:00–13:00 |
1002 | ДЭФ | Вторник | 08:00–13:00 |
1002 | ДЭФ | Среда | 08:00–13:00 |
1002 | ДЭФ | Четверг | 08:00–13:00 |
1002 | ДЭФ | Пятница | 08:00–13:00 |
1002 | ДЭФ | Суббота | 10:30–12:00 |
1002 | ДЭФ | Воскресенье | 10:30–13:00 |
1003 | GHE | Понедельник | 09:00–14:00 |
1003 | GHE | Вторник | 09:00–14:00 |
1003 | GHE | Среда | 09:00–14:00 |
1003 | GHE | Четверг | 09:00–14:00 |
1003 | GHE | Пятница | 09:00–14:00 |
1003 | GHE | Суббота | 10:30–12:00 |
1003 | GHE | Воскресенье | 10:30–12:00 |
1004 | IJK | Понедельник | 10:00–15:00 |
1004 | IJK | Вторник | 10:00–15:00 |
1004 | IJK | Среда | 10:00–15:00 |
1004 | IJK | Четверг | 10:00–15:00 |
1004 | IJK | Пятница | 10:00–14:00 |
1004 | IJK | Суббота | 10:30–12:00 |
1004 | IJK | Воскресенье | 10:30–12:00 |
1005 | ЛМН | Понедельник | 10:30–16:00 |
1005 | ЛМН | Вторник | 10:30–12:00 |
1005 | ЛМН | Среда | 10:30–12:00 |
1005 | ЛМН | Четверг | 10:30–12:00 |
1005 | ЛМН | Пятница | 10:30–14:00 |
1005 | ЛМН | Суббота | 10:30–12:00 |
1005 | ЛМН | Воскресенье | 10:30–12:00 |
Спасибо Семен за помощь
что ты уже испробовал? Что-либо?