1

Lets say my table looks like this : 

-------------------------
| id | prop             |
-------------------------
| 1  | Jhon_Dhoe_21     |
-------------------------
| 2  | Tom_Dohn_23_MALE |
-------------------------
| 3  | Scot             |
-------------------------

The properties will always be devided by a "_". So after the SELECT the table should look like this:

--------------------------------------
| id | prop1 | prop2 | prop3 | prop4 |
--------------------------------------
| 1  |  Jhon | Dhoe  |  21   |  NULL |
--------------------------------------
| 2  |  Tom  | Dohn  |  23   |  MALE |
--------------------------------------
| 3  |  Scot | NULL  | NULL  |  NULL |
--------------------------------------

Now if we know the maximum number of properties (n) that we could have I suppose we can create a n number of regex expresions over the prop column or something. But if we do not know maybe we have to first find the row with most properties ?

EDIT:

I can't accept multiple rows.

Lazar Lazarov
  • 2,412
  • 4
  • 26
  • 35
  • 2
    A SQL query can returns a fixed set of columns. If you want a variable number of columns you need to use dynamic SQL, or an alternative data structure (such as nested tables or JSON). – Gordon Linoff Jul 28 '16 at 11:44
  • @GordonLinoff I see .. thanks Gordon I will research about dynamic usage of SQL maybe it is finally time for it. – Lazar Lazarov Jul 28 '16 at 11:46
  • If you can accept multiple rows for each `id`, then this is a possible solution: http://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle. – Gordon Linoff Jul 28 '16 at 11:49
  • Well I can not but it would worked pretty good yes. – Lazar Lazarov Jul 28 '16 at 11:53

1 Answers1

2

it was an interesting question, so I've solved it this way:

with
  tbl as (
    select 1 id, 'Jhon_Dhoe_21' prop from dual union all
    select 2 id, 'Tom_Dohn_23_MALE' prop from dual union all
    select 3 id, 'Scot' prop from dual
  ),
  maxrows as (select level rn from dual connect by level <= 100)
select id, regexp_substr(t.prop, '[^_]+', 1, mr.rn) prop_rn, rn, prop
from tbl t, maxrows mr
where mr.rn <= regexp_count(t.prop, '\_') + 1
order by id, rn

Results:

        ID PROP_RN                  RN PROP
---------- ---------------- ---------- ----------------
         1 Jhon                      1 Jhon_Dhoe_21
         1 Dhoe                      2 Jhon_Dhoe_21
         1 21                        3 Jhon_Dhoe_21
         2 Tom                       1 Tom_Dohn_23_MALE
         2 Dohn                      2 Tom_Dohn_23_MALE
         2 23                        3 Tom_Dohn_23_MALE
         2 MALE                      4 Tom_Dohn_23_MALE
         3 Scot                      1 Scot

8 rows selected

If you know (or sure) of maximum possible columns, you can use:

with
  tbl as (
    select 1 id, 'Jhon_Dhoe_21' prop from dual union all
    select 2 id, 'Tom_Dohn_23_MALE' prop from dual union all
    select 3 id, 'Scot' prop from dual
  ),
  maxrows as (select level rn from dual connect by level <= 100),
  tbl2 as (
    select id, regexp_substr(t.prop, '[^_]+', 1, mr.rn) prop_rn, rn, prop
    from tbl t, maxrows mr
    where mr.rn <= regexp_count(t.prop, '\_') + 1
    order by id, rn)
select *
from tbl2
pivot (
  max(prop_rn)
  for rn in (1,2,3,4,6,7,8,9,10)
)

Result:

        ID PROP             1                2                3                4                6                7                8                9                10
---------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
         1 Jhon_Dhoe_21     Jhon             Dhoe             21                                                                                                    
         3 Scot             Scot                                                                                                                                    
         2 Tom_Dohn_23_MALE Tom              Dohn             23               MALE                                                                                 

SQL>

Or use xmltype:

with
  tbl as (
    select 1 id, 'Jhon_Dhoe_21' prop from dual union all
    select 2 id, 'Tom_Dohn_23_MALE' prop from dual union all
    select 3 id, 'Scot' prop from dual
  ),
  maxrows as (select level rn from dual connect by level <= 100),
  tbl2 as (
    select id, regexp_substr(t.prop, '[^_]+', 1, mr.rn) prop_rn, rn, prop
    from tbl t, maxrows mr
    where mr.rn <= regexp_count(t.prop, '\_') + 1
    order by id, rn)
select *
from tbl2
pivot xml (
  max(prop_rn) prp
  for rn in (any)
)
saphsys
  • 96
  • 4