I have table load_ext which is an external table for the below file structure
| customer | interval_type | data_count | Start_time | interval1 | interval2 | interval3 | ,..interval24 | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| 67891 | 60 | 5 | 06022022040000AM | 0.07 | 0.767 | 0.65 | 0.69 | 0 | 0... | ||
| 12345 | 60 | 8 | 06022022120000PM | 0.07 | 0.767 | 0.65 | 0.69 | 0.767 | 0.69 | 0 | 0 |
To explain the above columns, All columns are varchar2. Interval type is in minutes, data_count column says the number of intervals to be posted starting from the start_time column, Interval1 is the value for 00:00:00 to 01:00:00 AM and likewise. Target table will have the same structure but the above intervals should be moved to the respective columns. For example, the value of interval1 column in the first row should be moved to column interval4 and the same for all other columns to the respective interval periods.
My target table should have the data like below
| customer | interval_type | data_count | Start_time | interval1 | interval2 | interval3 | interval4 | interval5 | ..interval24 | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 67891 | 60 | 5 | 06022022040000AM | 0 | 0 | 0 | 0.07 | 0.767 | 0.65 | 0.81 | 0 | 0 | |||||
| 12345 | 60 | 8 | 06022022120000PM | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.07 | 0.65 | 0.07 | 0.65 | 0 | 0... |
I am providing the table data with ',' delimiter as the table structure is too big to post in the same format. This has to be done in Oracle only, we are using Oracle 19.
CodePudding user response：
Unpivot the columns to rows, add the hours from the start time to the interval and then pivot back to columns:
SELECT *
FROM (
SELECT customer,
interval_type
data_count,
start_time,
MOD(
interval_name TO_CHAR(TO_DATE(start_time, 'DDMMYYYYHH12MISSAM'), 'HH24'),
24
) AS interval_name,
value
FROM table_name
UNPIVOT (value FOR interval_name IN (
interval1 AS 00,
interval2 AS 01,
interval3 AS 02,
interval4 AS 03,
interval5 AS 04,
interval6 AS 05,
interval7 AS 06,
interval8 AS 07,
interval9 AS 08,
interval10 AS 09,
interval11 AS 10,
interval12 AS 11,
interval13 AS 12,
interval14 AS 13,
interval15 AS 14,
interval16 AS 15,
interval17 AS 16,
interval18 AS 17,
interval19 AS 18,
interval20 AS 19,
interval21 AS 20,
interval22 AS 21,
interval23 AS 22,
interval24 AS 23
))
)
PIVOT (
MAX(value) FOR interval_name IN (
00 AS interval24,
01 AS interval1,
02 AS interval2,
03 AS interval3,
04 AS interval4,
05 AS interval5,
06 AS interval6,
07 AS interval7,
08 AS interval8,
09 AS interval9,
10 AS interval10,
11 AS interval11,
12 AS interval12,
13 AS interval13,
14 AS interval14,
15 AS interval15,
16 AS interval16,
17 AS interval17,
18 AS interval18,
19 AS interval19,
20 AS interval20,
21 AS interval21,
22 AS interval22,
23 AS interval23
)
);
Which, for the sample data:
CREATE TABLE table_name (
customer,
interval_type,
data_count,
Start_time,
interval1,
interval2,
interval3,
interval4,
interval5,
interval6,
interval7,
interval8,
interval9,
interval10,
interval11,
interval12,
interval13,
interval14,
interval15,
interval16,
interval17,
interval18,
interval19,
interval20,
interval21,
interval22,
interval23,
interval24
) AS
SELECT 67891, 60, 5, '06022022040000AM', 0.07, 0.767, 0.65, 0.69, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 FROM DUAL UNION ALL
SELECT 12345, 60, 8, '06022022120000PM', 0.07, 0.767, 0.65, 0.69, 0.767, 0.69, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 FROM DUAL;
Outputs:
CUSTOMER DATA_COUNT START_TIME INTERVAL24 INTERVAL1 INTERVAL2 INTERVAL3 INTERVAL4 INTERVAL5 INTERVAL6 INTERVAL7 INTERVAL8 INTERVAL9 INTERVAL10 INTERVAL11 INTERVAL12 INTERVAL13 INTERVAL14 INTERVAL15 INTERVAL16 INTERVAL17 INTERVAL18 INTERVAL19 INTERVAL20 INTERVAL21 INTERVAL22 INTERVAL23 12345 60 06022022120000PM 0 0 0 0 0 0 0 0 0 0 0 0 .07 .767 .65 .69 .767 .69 0 0 0 0 0 0 67891 60 06022022040000AM 0 0 0 0 .07 .767 .65 .69 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
db<>fiddle here
CodePudding user response：
Here is one way to deal with it. First UNPIVOT the intervals, then index them (COLUMN_NO) and use MODEL clause to shift the values according to DATA_COUNT and finaly PIVOT it back again. Here is the sample with 12 intervals:
WITH
tbl AS
(
SELECT '67891' "CUSTOMER",
'60' "INTERVAL_TYPE",
'5' "DATA_COUNT",
'06022022040000AM' "START_TIME",
'0.07' "INTERVAL1",
'0.767' "INTERVAL2",
'0.65' "INTERVAL3",
'0.69' "INTERVAL4",
'0.62' "INTERVAL5",
'0.61' "INTERVAL6",
'0.70' "INTERVAL7",
'0.68' "INTERVAL8",
'0.62' "INTERVAL9",
'0.59' "INTERVAL10",
'0.69' "INTERVAL11",
'0.60' "INTERVAL12" FROM DUAL UNION ALL
--
SELECT '12345' "CUSTOMER",
'60' "INTERVAL_TYPE",
'8' "DATA_COUNT",
'06022022120000PM' "START_TIME",
'0.07' "INTERVAL1",
'0.767' "INTERVAL2",
'0.65' "INTERVAL3",
'0.69' "INTERVAL4",
'0.767' "INTERVAL5",
'0.69' "INTERVAL6",
'0.70' "INTERVAL7",
'0.68' "INTERVAL8",
'0.62' "INTERVAL9",
'0.59' "INTERVAL10",
'0.69' "INTERVAL11",
'0.60' "INTERVAL12" FROM DUAL
),
datarows AS
(
SELECT CUSTOMER, INTERVAL_TYPE, DATA_COUNT, START_TIME,
VALUE_NAME, VALUE_OF
FROM tbl
UNPIVOT (
VALUE_OF FOR VALUE_NAME
IN (INTERVAL1, INTERVAL2, INTERVAL3, INTERVAL4, INTERVAL5, INTERVAL6,
INTERVAL7, INTERVAL8, INTERVAL9, INTERVAL10, INTERVAL11, INTERVAL12)
)
),
dataset AS
(
SELECT
CUSTOMER, INTERVAL_TYPE, DATA_COUNT, START_TIME,
REPLACE(VALUE_NAME, 'INTERVAL', '') "COLUMN_NO",
VALUE_NAME,
VALUE_OF,
VALUE_OF "ORIG_VALUE"
FROM
datarows
),
combined AS
(
SELECT
CUSTOMER,
INTERVAL_TYPE,
DATA_COUNT,
START_TIME,
COLUMN_NO,
VALUE_NAME,
Nvl(VALUE_OF, '0') "VALUE_OF",
ORIG_VALUE
FROM
dataset
MODEL
PARTITION BY (CUSTOMER)
DIMENSION BY (COLUMN_NO, DATA_COUNT)
MEASURES (VALUE_OF, VALUE_NAME, INTERVAL_TYPE, START_TIME, ORIG_VALUE)
RULES ITERATE(12)
(
VALUE_OF[ITERATION_NUMBER 1, ANY] = CASE
WHEN CV(COLUMN_NO) - To_Number(CV(DATA_COUNT)) 1 < 0 - To_Number(CV(DATA_COUNT)) - 1 --CV(COLUMN_NO)
THEN '0'
ELSE
ORIG_VALUE[To_Char(To_Number(CV(COLUMN_NO)) - To_Number(CV(DATA_COUNT)) 2), CV(DATA_COUNT)]
END
)
)
SELECT * FROM
(
SELECT
CUSTOMER,
INTERVAL_TYPE,
DATA_COUNT,
START_TIME,
VALUE_NAME,
VALUE_OF
FROM
combined
WHERE CUSTOMER = '67891'
)
PIVOT(
MAX(VALUE_OF)
FOR VALUE_NAME
IN ('INTERVAL1', 'INTERVAL2', 'INTERVAL3', 'INTERVAL4', 'INTERVAL5', 'INTERVAL6',
'INTERVAL7', 'INTERVAL8', 'INTERVAL9', 'INTERVAL10', 'INTERVAL11', 'INTERVAL12')
)
UNION ALL
SELECT * FROM
(
SELECT
CUSTOMER,
INTERVAL_TYPE,
DATA_COUNT,
START_TIME,
VALUE_NAME,
VALUE_OF
FROM
combined
WHERE CUSTOMER = '12345'
)
PIVOT(
MAX(VALUE_OF)
FOR VALUE_NAME
IN ('INTERVAL1%' AS INTERV1, 'INTERVAL2', 'INTERVAL3', 'INTERVAL4', 'INTERVAL5', 'INTERVAL6',
'INTERVAL7', 'INTERVAL8', 'INTERVAL9', 'INTERVAL10', 'INTERVAL11', 'INTERVAL12')
)
--
--
-- CUSTOMER INTERVAL_TYPE DATA_COUNT START_TIME 'INTERVAL1' 'INTERVAL2' 'INTERVAL3' 'INTERVAL4' 'INTERVAL5' 'INTERVAL6' 'INTERVAL7' 'INTERVAL8' 'INTERVAL9' 'INTERVAL10' 'INTERVAL11' 'INTERVAL12'
-- -------- ------------- ---------- ---------------- ------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------------ ------------ ------------
-- 67891 60 5 06022022040000AM 0 0 0.07 0.767 0.65 0.69 0.62 0.61 0.70 0.68 0.62
-- 12345 60 8 06022022120000PM 0 0 0 0 0 0.07 0.767 0.65 0.69 0.767 0.69
Hopefully, you could use it to solve the problem. Regards...