Good Day
I want to ask for your valuable help on a problem that I found, there is a table that stores the information in a single column in Clob format and is copied to another table of a second Oracle Schema, which separates the information with a special character "", The problem arises at the moment of extracting the information of each row, since this one is stored of the following way:
ID Data
23 23¿A1.0¿B1.0¿C1.0¿D1.0¿E1.0¿F1.0¿G1.0
67 67¿A1.0¿A1.1¿B1.0¿B1.1¿C1.0¿C1.1¿D1.0¿D1.1¿E1.0¿E1.1¿F1.0¿F1.1¿G1.0¿G1.1
123 123¿A1.0¿A1.1¿A1.2¿A1.3¿A1.4¿B1.0¿B1.1¿B1.2¿B1.3¿B1.4¿C1.0¿C1.1¿C1.2¿C1.3¿C1.4¿D1.0¿D1.1¿D1.2¿D1.3¿D1.4¿E1.0¿E1.1¿E1.2¿E1.3¿E1.4¿F1.0¿F1.1¿F1.2¿F1.3¿F1.4¿G1¿G1.1¿G1.2¿G1.3¿G1.4
And I have not found a way to sort the information like this:
ID Data1 Data2 Data3 Data4 Data5 Data6
23 A1.0 B1.0 C1.0 D1.0 F1.0 G1.0
67 A1.0 B1.0 C1.0 D1.0 F1.0 G1.0
67 A1.1 B1.1 C1.1 D1.1 F1.1 G1.1
123 A1.0 B1.0 C1.0 D1.0 F1.0 G1.0
123 A1.1 B1.1 C1.1 D1.1 F1.1 G1.1
123 A1.2 B1.2 C1.2 D1.2 F1.2 G1.2
123 A1.3 B1.3 C1.3 D1.3 F1.3 G1.3
123 A1.4 B1.4 C1.4 D1.4 F1.4 G1.4
If you could advise me on how I can carry out this process, I would be grateful.
Your special character appears to have been mangled... Whatever the character is, you can use this method:
- Split the values out into rows
- Pivot them back into columns based on the last character in the string:
create table t (
id int,
vals varchar2(4000)
);
insert into t values (23, '23¿A1.0¿B1.0¿C1.0¿D1.0¿E1.0¿F1.0¿G1.0');
insert into t values (67, '67¿A1.0¿A1.1¿B1.0¿B1.1¿C1.0¿C1.1¿D1.0¿D1.1¿E1.0¿E1.1¿F1.0¿F1.1¿G1.0¿G1.1');
insert into t values (123, '123¿A1.0¿A1.1¿A1.2¿A1.3¿A1.4¿B1.0¿B1.1¿B1.2¿B1.3¿B1.4¿C1.0¿C1.1¿C1.2¿C1.3¿C1.4¿D1.0¿D1.1¿D1.2¿D1.3¿D1.4¿E1.0¿E1.1¿E1.2¿E1.3¿E1.4¿F1.0¿F1.1¿F1.2¿F1.3¿F1.4¿G1.0¿G1.1¿G1.2¿G1.3¿G1.4');
with vals as (
select id, trim(regexp_substr(t.vals, '[^¿]+', 1, rws.column_value)) as val
from t,
table ( cast ( multiset(
select level from dual connect by level <= length (regexp_replace(t.vals, '[^¿]+')) + 1
) as sys.OdciNumberList ) ) rws
), splits as (
select substr(val, 1, 1) st, substr(val, -1, 1) en, val, id from vals
where substr(val, 1, 1) in ('A', 'B', 'C', 'D', 'F', 'G')
)
select id, dat1, dat2, dat3, dat4, dat5, dat6 from splits
pivot (min(val) for st in ('A' dat1, 'B' dat2, 'C' dat3, 'D' dat4, 'F' dat5, 'G' dat6))
order by id, en;
ID DAT1 DAT2 DAT3 DAT4 DAT5 DAT6
23 A1.0 B1.0 C1.0 D1.0 F1.0 G1.0
67 A1.0 B1.0 C1.0 D1.0 F1.0 G1.0
67 A1.1 B1.1 C1.1 D1.1 F1.1 G1.1
123 A1.0 B1.0 C1.0 D1.0 F1.0 G1.0
123 A1.1 B1.1 C1.1 D1.1 F1.1 G1.1
123 A1.2 B1.2 C1.2 D1.2 F1.2 G1.2
123 A1.3 B1.3 C1.3 D1.3 F1.3 G1.3
123 A1.4 B1.4 C1.4 D1.4 F1.4 G1.4
This, of course, assumes that you always have A, B, C, etc. Or at least you want these values to appear in the same column if some are missing!