create table test001 (cuss varchar2(10), acc varchar2(30)); insert into

I have a tablle like this.
cust acc
---------------
a 1
b 2|3
c 4|5|6

I Want below o/p:
cust acc
-----------
a 1
b 2
b 3
c 4
c 5
c 6
Please any one can you have any ideas share me.
I have urgent requirement.

Question Posted / kavitha nedigunta

4 Answers
12431 Views
Cap Gemini, MTS, I also Faced
E-Mail Answers

Answer Posted / kavitha nedigunta

create table test001 (cuss varchar2(10), acc varchar2(30));

insert into test001 values ('a','1');

insert into test001 values ('b','2|3');

insert into test001 values ('c','4|5|6');

WITH CTE AS (SELECT CUSS,ACC FROM TEST001)
select distinct trim(regexp_substr( acc, '[^|]+', 1,
level)) acc ,cuss from cte
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(ACC, '[^|]+')) +
1;

Is This Answer Correct ?

3 Yes

3 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

Can we rollback truncate?

1007

what is primary key? : Sql dba

1035

What is the difference between inner join and outer join?

1002

What is sql basics?

1047

how to rename an existing table in mysql? : Sql dba

1068

Name three sql operations that perform a sort.

1019

how many triggers are allowed in mysql table? : Sql dba

1078

What is materialized view. What are different methods of refresh?

1395

Can a primary key be a foreign key?

1030

What are different types of statements supported by sql?

1172

What is cartesian join in sql?

1138

what is sub-query? : Transact sql

1108

What is normalization in sql?

1026

describe transaction-safe table types in mysql : sql dba

1011

What is t-sql? : Transact sql

996