You Asked
I have a table as below
R_ID R_Site R_Name R_Role
----- ------------- ------ ------
1 123,-456,-789 qwer Owner
2 56,-741-852 qaz Manager
3 369,-741,-987 wsx Employee
4 All eddc Employee
5 All rfv Employee
And used the below query to convert the R_Site Comma separated values into rows
select REGEXP_SUBSTR (txt, '[^,]+', 1, level) as SITE , R_NAME
from (select distinct R_SITE as txt, R_NAME from MY_TABLE
connect by level <= length(regexp_replace(txt,'[^,]*'))+1
Now I need to join the above result set to the table like below one on R_SITE(SITE) = S_SITE to get S_Sal value in the final result
S_ID S_Site S_Sal S_Mgr
----- ------ ----- -----
1 -789 10000 Raghu
2 -123 20000 Mahesh
3 -456 300000 Swetha
4 -147 400000 Vamsi
5 All 500000 Narendra
But when I join, the db has been spinning for ever and not giving any result.
Could you please let me know if I'm doing wrong in any step and ways to correct it.
SQL Updates:
---------
Source table
CREATE TABLE MY_TABLE(
R_ID int,
R_Site varchar(255),
R_Name varchar(255),
R_Role varchar(255)
);
INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('1' , '123,-456,-789', 'qwer' , 'Owner');
INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('2' , '56,-741,-852', 'qaz' , 'Manager');
I would like to have the result like the below table after splitting the comma separated string to rows
CREATE TABLE MY_TABLE(
R_ID int,
R_Site varchar(255),
R_Name varchar(255),
R_Role varchar(255)
);
INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('1' , '123', 'qwer' , 'Owner');
INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('1' , '-456,', 'qwer' , 'Owner');
INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('1' , '-789', 'qwer' , 'Owner');
INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('2' , '56', 'qaz' , 'Manager');
INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('2' , '-741', 'qaz' , 'Manager');
INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('2' , '-852', 'qaz' , 'Manager');
I am able to achieve this effectively by using regular exp and connect by level. But I would like to have a solution in ASCI standard.
Thanks in advance.
and Connor said...
Thanks for putting it into SQL so we can work with it.
Even though you put "18" for the version, I'm going to assume thats a SQL Developer version, so here is something that will work in 11g. A spinning query often results if your calculation of the number of commas is wrong, so you end up "connect by"-ing forever. We can use cast/multiset to get the number of commas *per row* to avoid this
SQL> CREATE TABLE MY_TABLE(
2 R_ID int,
3 R_Site varchar(20),
4 R_Name varchar(20),
5 R_Role varchar(20)
6 );
Table created.
SQL>
SQL> INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
2 VALUES ('1' , '123,-456,-789', 'qwer' , 'Owner');
1 row created.
SQL>
SQL> INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
2 VALUES ('2' , '56,-741,-852', 'qaz' , 'Manager');
1 row created.
SQL>
SQL>
SQL> select
2 t.r_id,
3 t.r_name,
4 t.r_role,
5 regexp_substr(t.R_Site, '[^,]+', 1, commas.column_value) as site
6 from
7 MY_TABLE t,
8 table(cast(multiset(select level from dual connect by level <= length (regexp_replace(t.R_Site, '[^,]+')) + 1) as sys.OdciNumberList)) commas
9 order by 1,2,3,4;
R_ID R_NAME R_ROLE SITE
---------- ------------------------------------------------------------ ------------------------------------------------------------ --------------------
1 qwer Owner -456
1 qwer Owner -789
1 qwer Owner 123
2 qaz Manager -741
2 qaz Manager -852
2 qaz Manager 56
6 rows selected.
From that point, that result set is just like any other table, so if you want to join with something else, a WITH clause keeps it simple, ie
with my_Results as
(
select
t.r_id,
t.r_name,
t.r_role,
regexp_substr(t.R_Site, '[^,]+', 1, commas.column_value) as site
from
MY_TABLE t,
table(cast(multiset(select level from dual connect by level <= length (regexp_replace(t.R_Site, '[^,]+')) + 1) as sys.OdciNumberList)) commas
)
select ...
from my_results,
[other table]
where ...
I'm not sure what you mean by not ANSI standard - if by that you mean you do not want to use CONNECT BY then there are other ways. Here's a nice summary:
https://lalitkumarb.wordpress.com/2015/03/04/split-comma-delimited-strings-in-a-table-in-oracle/
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment