StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I don't think this is an exact duplicate of the question referenced in the close votes. Similar yes, but not the same.</p> <p>Not exactly beautiful, but:</p> <pre><code>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; </code></pre> <p>where tt is your table.</p> <p>Works for csv values longer than 1 or null. The CONNECT BY LEVEL < 20 is arbitrary, adjust for your situation.</p> <p>To illustrate:</p> <pre><code> 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> </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload