I have a tablle like this. cust acc --------------- a 1 b

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 / basha

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

Answers were Sorted based on User's Feedback

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

Answer / 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

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

Answer / p deshmukh

SELECT distinct COL, trim(regexp_substr(colvalue, '[^|]+', 1, level)) colvalue
FROM (with demo as ( select '1' as a, '2|3' as b, '4|5|6' as c from dual )

select * from demo
unpivot
( colvalue for col in (a, b, c) )) t
CONNECT BY instr(colvalue, '|', 1, level - 1) > 0
order by col

Is This Answer Correct ?

0 Yes

0 No

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

Answer / punith

SELECT a,
REGEXP_SUBSTR (b, '([^|]+)', 1, lvl) b

FROM dummy,
(SELECT LEVEL lvl
FROM (SELECT MAX (LENGTH (REGEXP_REPLACE
(b, '[^|]'))) mx
FROM dummy)
CONNECT BY LEVEL <= mx + 1)
WHERE lvl - 1 <= LENGTH (REGEXP_REPLACE (b, '[^|]'));

Is This Answer Correct ?

0 Yes

2 No

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

Answer / monu singh shekhawat

select * from tab
group by acc

Is This Answer Correct ?

1 Yes

7 No

Post New Answer