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.
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
What is varchar data type in sql?
What are procedures used for?
What is sql table?
What is a table partition?
What are the advantages of sql? Explain
Can a composite key be null?
What are the syntax and use of the coalesce function?
Explain the difference between cursor declared in procedures and cursors declared in the package specification?
Is full outer join same as cross join?
How do you exit in sql?
What are Lexical Parameters.How They are used in Reports 6i
What is cross join sql?