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 |
how can we write a column values horizontal by using sql stmt; ex: select name from table_name;(actual output) a b c d require output is a b c d
5 Answers Honeywell, Interact,
Show the two pl/sql cursor exceptions.
what are the different tables present in mysql? : Sql dba
What are the benefits of pl sql?
Who is the owner of mysql database?
How to generate a salary slip like jan 1000 1000 feb 1000 2000 ... dec 1000 12000
What are all ddl commands?
Can we call dml statement in function?
Explain UNION,MINUS,UNION ALL, INTERSECT?
can we call a procedure into another procedure?If yes means how you can pass the perameters for the two procedures?
what is the difference between implicit conversions and explicit conversions?
Write a sql query to convert all character to uppercase after hypen.