E. Raznovskiy E. Raznovskiy - 18 days ago 7
SQL Question

How to split sequences and unpivot this table?

There's a task. I need to transpose this table:

id value
1001 param1='afvr';param2='ghx';param3='';...;paramN='zfdf'
1002 param1='arthr';param2='reger';param3='';....;paramM='zehe'
1003 param1='qwer';param2='geiew';param3='';...;paramX='qweio'
...... ......
2001 param1='pawoe';param2='eug';param3='';.....;paramn='mds'


into this:

id param1 param1 param3 paramN
1001 'afvr' 'ghx' null 'zfdf'
1002 'arthr' 'reger' null 'zehe'
1003 'qwer' 'geiew' null 'qweio'
...... ......
2001 'pawoe' 'eug' null 'mds'


and this:

id value
1001 'afvr'
1001 'ghx'
1001 null
1001 'zfdf'
1002 'arthr'
1002 'reger'
1002 null
1002 'zehe'
1003 'qwer'
1003 'geiew'
1003 null
1003 'qweio'
...... ......
2001 'pawoe'
2001 'eug'
2001 null
2001 'mds'


Using SQL and regular expressions. Although I understand that I should use loops, I don't get it. Is there a way to do it using Oracle?

Answer

If you are only trying to get to the final result shown in your third table, and don't actually need the intermediate view of the data, you could treat the value as parameters in a dummy XML node, and then use XMLTable to extract all of the parameter values:

select yt.id, x.value
from your_table yt
cross join xmltable('/tmp/@*'
  passing xmltype('<tmp ' || translate(value, ';', ' ') || ' />')
  columns value varchar2(30) path '.'
) x;

Quick demo with a CTE supplying the partial sample data from your question, plus another row to demonstrate a couple of scenarios raised in comments:

with your_table (id, value) as (
  select 1001, q'[param1='afvr';param2='ghx';param3='';paramN='zfdf']' from dual
  union all select 1002, q'[param1='arthr';param2='reger';param3='';paramM='zehe']' from dual
  union all select 1003, q'[param1='qwer';param2='geiew';param3='';paramX='qweio']' from dual
  union all select 2001, q'[param1='pawoe';param2='eug';param3='';paramn='mds']' from dual
  union all select 2002, q'[param1='abc def';param2='ghi;jkl';param3='m=n']' from dual
)
select yt.id, x.value
from your_table yt
cross join xmltable('/tmp/@*'
  passing xmltype('<tmp ' || translate(value, ';', ' ') || ' />')
  columns value varchar2(30) path '.'
) x;

        ID VALUE                         
---------- ------------------------------
      1001 afvr                          
      1001 ghx                           
      1001                               
      1001 zfdf                          
      1002 arthr                         
      1002 reger                         
      1002                               
      1002 zehe                          
      1003 qwer                          
      1003 geiew                         
      1003                               
      1003 qweio                         
      2001 pawoe                         
      2001 eug                           
      2001                               
      2001 mds                           
      2002 abc def                       
      2002 ghi jkl                       
      2002 m=n                           

19 rows selected. 

I've guessed the maximum size of the value, adjust the (30) if necessary. This converts the semicolon separators to spaces so they are valid in XML; you could also use replace(). The bit inside the XMLType constructor ends up as:

        ID TMP_VALUE                                                    
---------- -------------------------------------------------------------
      1001 <tmp param1='afvr' param2='ghx' param3='' paramN='zfdf' />   
      1002 <tmp param1='arthr' param2='reger' param3='' paramM='zehe' />
      1003 <tmp param1='qwer' param2='geiew' param3='' paramX='qweio' />
      2001 <tmp param1='pawoe' param2='eug' param3='' paramn='mds' />   
      2002 <tmp param1='abc def' param2='ghi jkl' param3='m=n' />       

and the XMLTable looks for all the parameters within than dummy tmp node, whatever they are called.

If you might have a semicolon within a parameter value and not just as a separator between them then you need to be more careful, since that will be lost - as in the second-to-last value above. You can void that with a regular expression, which will be slower (there may well be a better pattern):

select yt.id, x.value
from your_table yt
cross join xmltable('/tmp/@*'
  passing xmltype('<tmp ' || regexp_replace(value, q'[';param]', q'[' param]') || ' />')
  columns value varchar2(30) path '.'
) x;

        ID VALUE                         
---------- ------------------------------
...
      2002 abc def                       
      2002 ghi;jkl                       
      2002 m=n                           

You could even convert each parameter to its own node and change the XPath to match, but not sure that gains you anything.

However, the converted string needs to still be less that 4000 bytes in 11g, including the dummy node construction (<tmp ... />). That doesn't look likely to be a problem, but if your current table column is 4000 characters (or bytes) and might be completely filled, it's feasible you'd end up with a string that's just too long; in which case you can change the word 'param' to something shorted in the regex version. The actual name is never used.

Comments