Skip to Main Content
  • Questions
  • Convert row to matrix and save in table

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Alberto.

Asked: September 23, 2017 - 4:50 pm UTC

Last updated: October 11, 2017 - 2:53 pm UTC

Version: Express Edition 11G

Viewed 1000+ times

You Asked

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.

and Chris said...

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!

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Vector to Matrix

Alberto Ruiz, October 17, 2017 - 4:07 am UTC

Thanks for the help, I followed the example you have placed and I have been able to solve the problem.

Blessings.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.