write a sql query following source looking like below
column1 column2
101,102,103 abc,def,ghi
1001,1002,1003 a,b,c
i want the output
column1 column1
101 abc
102 def
103 ghi
1001 a
1002 b
1003 c
- 1 Answers
- 4092 Views
- KPIT, I also Faced
- E-Mail Answers
Answer / farrukhshaikh
select vw1.str, vw2.str
from (select rownum sr,
substr(col,
instr(col, ',', 1, level) + 1,
instr(col, ',', 1, level + 1) -
instr(col, ',', 1, level) - 1) str
from (select ',' || qry || ',' col
from (select '101,102,103,1001,1002,1003'
qry from dual))
connect by level <= length(col) - length(replace
(col, ',')) - 1) vw1,
(select rownum sr,
substr(col,
instr(col, ',', 1, level) + 1,
instr(col, ',', 1, level + 1) -
instr(col, ',', 1, level) - 1) str
from (select ',' || qry || ',' col
from (select 'abc,def,ghi,a,b,c' qry from
dual))
connect by level <= length(col) - length(replace
(col, ',')) - 1) vw2
where vw1.sr = vw2.sr
| Is This Answer Correct ? | 1 Yes | 0 No |
Explain a data segment?
can anyody please send me the dump for Oracle 10g certifications for DBA path?
what is the maximum number of indexes i can create for a table? What happens if i create indexes for all the columns of a table? Will it slow down the speed of retrieval
Query to retrieve record for a many to many relationship ?
i deleted 4 records form the table.after i have applied commit .now i want to get back those records.how?
What is an oracle database table?
How would you best determine why your MVIEW couldnt FAST REFRESH?
What is meant by an index?
How to select oracle sequence from different schema and How to select oracle sequence from different Databases. Explain with example
How to enter a new row into a table interactively?
What is a Sequence ?
How do I uninstall oracle client from windows?
Oracle General 
