Create a view/temporary table from a column with CSV [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

Comma Separated values in Oracle

Folks, I know its an exteremly bad idea, and this table needs to be normal开发者_如何转开发ized. But unfortunately I cannot change the schema.

We have a table in Oracle DB , as

id|value   | other_columns
----------------------------
 1|a,b,c   |some values

Can we create a view with something like

id|value   
-----------
 1|a
 1|b
 1|c

Thanks in advance for help.

---

I don't think this is an exact duplicate of the question referenced in the close votes. Similar yes, but not the same.

Not exactly beautiful, but:

CREATE OR REPLACE VIEW your_view AS
SELECT tt.ID, SUBSTR(value, sp, ep-sp) split, other_col1, other_col2...
  FROM (SELECT id, value
             , INSTR(','||value, ',', 1, L) sp  -- 1st posn of substr at this level
             , INSTR(value||',', ',', 1, L) ep  -- posn of delimiter at this level
          FROM tt JOIN (SELECT LEVEL L FROM dual CONNECT BY LEVEL < 20) q -- 20 is max #substrings
                    ON LENGTH(value)-LENGTH(REPLACE(value,','))+1 >= L 
) qq JOIN tt on qq.id = tt.id;

where tt is your table.

Works for csv values longer than 1 or null. The CONNECT BY LEVEL < 20 is arbitrary, adjust for your situation.

To illustrate:

    SQL> CREATE TABLE tt (ID INTEGER, c VARCHAR2(20), othercol VARCHAR2(20));

    Table created
    SQL> INSERT INTO tt VALUES (1, 'a,b,c', 'val1');

    1 row inserted
    SQL> INSERT INTO tt VALUES (2, 'd,e,f,g', 'val2');

    1 row inserted
    SQL> INSERT INTO tt VALUES (3, 'a,f', 'val3');

    1 row inserted
    SQL> INSERT INTO tt VALUES (4,'aa,bbb,cccc', 'val4');

    1 row inserted
    SQL> CREATE OR REPLACE VIEW myview AS
      2  SELECT tt.ID, SUBSTR(c, sp, ep-sp+1) splitval, othercol
      3    FROM (SELECT ID
      4               , INSTR(','||c,',',1,L) sp, INSTR(c||',',',',1,L)-1 ep
      5            FROM tt JOIN (SELECT LEVEL L FROM dual CONNECT BY LEVEL < 20) q
      6                      ON LENGTH(c)-LENGTH(REPLACE(c,','))+1 >= L
      7  ) q JOIN tt ON q.id =tt.id;

    View created
    SQL> select * from myview order by 1,2;

                                     ID SPLITVAL             OTHERCOL
--------------------------------------- -------------------- --------------------
                                      1 a                    val1
                                      1 b                    val1
                                      1 c                    val1
                                      2 d                    val2
                                      2 e                    val2
                                      2 f                    val2
                                      2 g                    val2
                                      3 a                    val3
                                      3 f                    val3
                                      4 aa                   val4
                                      4 bbb                  val4
                                      4 cccc                 val4

12 rows selected

SQL> 

---

I did something similiar to this in the past. You need to create a function that accepts an input string and a separator and returns a dataset. If separator is ommited, then comma is assumed.

First create a new type that represents a table of strings:

create or replace type varcharTableType as table of varchar2(255);
/

Then create this function:

create or replace function splitString(
  allValues in varchar2,
  delim in varchar2 default ','
)
return varcharTableType
as
  str     varchar2(255) := allValues || delim;
  pos     number;
  dataset varcharTableType := varcharTableType();
begin
  loop
    pos := instr(str, delim);
    exit when (nvl(pos, 0) = 0);
    dataset.extend;
    dataset(dataset.count) := ltrim(rtrim(substr(str, 1, pos - 1)));
    str := substr(str, pos + length(delim));
  end loop;
  return dataset;
end;
/

Finally, call as:

select *
  from table(cast(splitString('a,b,c') as varcharTableType));

COLUMN_VALUE                                                                                                                                                                                                                                                    
---------------
a                                                                                                                                                                                                                                                            
b                                                                                                                                                                                                                                                         
c                                                                                                                                                                                                                                                           

3 rows selected

To answer your specific case, you simply need to create a view that joins your table with this function table, as:

  create or replace view splitView as
    select yourTable.id, s.column_value as value
      from yourTable,
           table(cast(splitString(yourTable.value) as varcharTableType)) s;


  select * from splitView;

    id   value                                                                                                                                                                                                                                                    
    ---- ---------------
    1    a                                                                                                                                                                                                                                                            
    1    b                                                                                                                                                                                                                                                         
    1    c                                                                                                                                                                                                                                                           

    3 rows selected

I am not sure if this last query will work, as I don't have an Oracle machine right now, but hopefully should help you.