Thanks for the question, Anders.

Asked: February 26, 2018 - 7:32 pm UTC

Last updated: March 20, 2018 - 11:11 am UTC

Version: Oracle Database 11g

Viewed 1000+ times

You Asked

Hi,

I'm working with migrating data into a system using a migration tool supplied by the vendor of the target system.
The tool uses a predefined schema and we are getting the data in non normalized csv format. So we need to transform
the data into the migration schema.
So here I have a table

create table t ( ID int, Prop1 varchar2(30), Prop2 varchar2(30), Prop3 varchar2(30), Prop4 varchar2(30) );

with this data in it

insert into t values ( 12345, 'abc', 'abc', 'abc%20%def', 'def' );

I need to transform that into this: (The value in Prop3 is splited on '%20%)

ID  PropName  Value
12345  Prop1   abc
12345  Prop2   abc
12345  Prop3   abc
12345  Prop3   def
12345  Prop4   def

Today I use:

CURSOR cur_temp
IS
SELECT *
    FROM t UNPIVOT (PROPVALUE FOR PROPNAME IN(
    PROP1,PROP2,PROP3,PROP4));

Then I BULK COLLECT that into a table type
process it with PL/SQL using this nice query that I found on this site

    (select substr (x,
      instr (x, %20%, 1, level ) + 4,
      instr (x, %20%, 1, level+1) - instr (x, %20%, 1, level) - 4)
      as IBA_SINGLE_VAL
    from (select %20%||l_org_att_wtpart(indx).STRINGVALUE||%20% as x from dual)
    connect by instr (x, %20%, 1, level+1) > 0)

Then I use FORALL to insert the values.

The current solution works, but has not been tested on larger data sets. In an up coming migration we have a data
set where the table t will have around 2 miljon records and 20 prop columns so I a little concerned about performance.
I know your clear recommendations is to first try to use a single SQL statement and if that fails use PL/SQL.

Is this possible to do in SQL?

Thanks
Anders

and Chris said...

Sure it's possible in one statement. All you need to do is pass the unpivoted data to an adapted csv-to-rows solution. Look for %20% instead of commas.

Here's an example using regular expressions:

create table t ( 
  ID int, Prop1 varchar2(30), Prop2 varchar2(30), 
  Prop3 varchar2(30), Prop4 varchar2(30) 
);
insert into t values ( 12345, 'abc', 'abc', 'abc%20%def', 'def' );

with rws as (
  select level x from dual
  connect by level <= 10 -- ensure >= number of %20% occurrences
), unpiv as (
  select *
  from   t unpivot (
    propvalue for propname in(prop1,prop2,prop3,prop4)
  )
)
  select regexp_count(propvalue, '%20%'), 
         regexp_substr(propvalue, '[^(%20%)]+', 1, x) val,
         unpiv.* 
  from   unpiv
  join   rws
  on     regexp_count(propvalue, '%20%') + 1 >= x 
  order  by id, propname, val;

REGEXP_COUNT(PROPVALUE,'%20%')   VAL   ID      PROPNAME   PROPVALUE    
                               0 abc     12345 PROP1      abc          
                               0 abc     12345 PROP2      abc          
                               1 abc     12345 PROP3      abc%20%def   
                               1 def     12345 PROP3      abc%20%def   
                               0 def     12345 PROP4      def

You can find many other csv-to-rows examples on this site.

Rating

(3 ratings)

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

Comments

Quick and clear answer

Anders Kullenberg, February 28, 2018 - 8:01 am UTC

Very quick and clear answer which gave me example I can use for more than this specific problem.

Comment on the regexp

Anders Kullenberg, February 28, 2018 - 1:25 pm UTC

The regexp searches for any characters that is not a %, 2 or 0. So if any of these characters exist in the actual values needed this will not work. The regexp version works when splitting on single character. Splitting on a multiple character I think the substr/instr query works better:

with rws as (
select level x from dual
connect by level <= 10 -- ensure >= number of %20% occurrences
), unpiv as (
select *
from t unpivot (
propvalue for propname in(prop1,prop2,prop3,prop4)
)
)
select id,
propname,
substr (('%20%'||propvalue||'%20%'),
instr (('%20%'||propvalue||'%20%'), '%20%', 1, x ) + 4,
instr (('%20%'||propvalue||'%20%'), '%20%', 1, x+1) - instr (('%20%'||propvalue||'%20%'), '%20%', 1, x) - 4) val
from unpiv
join rws
on regexp_count(propvalue, '%20%') + 1 >= x ;

February 28, 2018 - 3:49 pm UTC

Great point. Or you could use regular replace to map %20% to a single other known "impossible" value.

Passing Dynamic Cols into UNPIVOT

Sivakumar Damodharan, March 19, 2018 - 9:37 pm UTC

I running into a situation where i have to use UNPIVOT but i want to try something dynamically because the number of columns will change depends on the table i am going to pass. I have so far achieved something basically with help of virtual column but i am unable to use that in a procedure.

DECLARE mainobject varchar(100);
BEGIN
select 'A_' || coalesce(extendsobject,entityname) as mainobject into mainobject from maxobject where objectname = (select maintbname from maxapps where app ='LEAKSR');
END;

COLUMN cols NEW_VALUE cols

SELECT LISTAGG(attributename, ', ') WITHIN GROUP (ORDER BY attributename) AS COLS
from MAXIMO. maxattribute
where objectname = 'TICKET' and eauditenabled = 1 and maxtype != 'YORN';

select &cols, eaudittimestamp, EAUDITUSERNAME from A_TICKET where ticketid = '17-100018';

SELECT ASSETNUM AS DATA_FIELD, EAUDITTIMESTAMP, EAUDITUSERNAME, VALUE
FROM A_TICKET
UNPIVOT
(VALUE FOR ASSETNUM IN (&cols))
WHERE ticketid = '17-100018'
ORDER BY DATA_FIELD, EAUDITTIMESTAMP;

Please advice me how i can achieve this dynamically

March 20, 2018 - 11:11 am UTC

You want to change the columns for the unpivot IN list?

You'll have to go with dynamic SQL.

Unless you're lucky enough to be on 18c. Here you could use Polymorphic Table Functions to do the job:

https://livesql.oracle.com/apex/livesql/file/content_GBB2BVOTJNDI1L4EUAWCQUBIB.html

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Quick and clear answer

Comment on the regexp

Passing Dynamic Cols into UNPIVOT

More to Explore

SQL

Unpivot columns and values

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Quick and clear answer

Comment on the regexp

Passing Dynamic Cols into UNPIVOT

More to Explore

SQL