Hi,
Ive a simple table with the below sample dataset,
Table: Inventory(keyID number primary key, DeptNumber clob,Names varchar2(100));
keyID DeptNumber Names
=============================================
13 4590,2391 ABCX
11 3500 GHYR
15 9748 JRFDS
17 5900,6789,3571 FEDSS
18 6136, 5371 SDDFH
20 6715 TEST2
21 4673 BOOKS
Now iam trying to create a query/sql to split the deptnumber based on delimiter "," and format it as links/URL and reconnect them together as string below,
select keyID, DeptNumber, <<EXPRESSIONS>> as URLS FROM Inventory;
keyID, DeptNumber, <<EXPRESSIONS>>
----------------------------------------------------------------------
13 4590,2391 <a href="URL">4590</a>, <a href="URL">2391</a>
11 3500 <a href="URL">3500</a>
15 9748 <a href="URL">9748</a>
17 5900,6789,3571 <a href="URL">5900</a>, <a href="URL">6789</a>, <a href="URL">3571</a>
18 6136, 5371 <a href="URL">6136</a>, <a href="URL">5371</a>
20 6715 <a href="URL">6715</a>
21 4673 <a href="URL">4673</a>
SELECT keyID, DeptNumber, LISTAGG(LNK,',') WITHIN GROUP (ORDER BY LNK) AS URL FROM
( SELECT keyID, DeptNumber,
( select 'a href="">'|| regexp_substr(DeptNumber,'[^,]+', 1, level)||'</a>' AS LNK
from dual
connect BY regexp_substr(DeptNumber, '[^,]+', 1, level)
is not null ) AS LNK FROM INVENTORY )
mysource order by 1,2
however my query output is not generating the correct expression/urls so whats the best or correct approach here?
Using Oracle 12.x C
thanks.