Oracle Query to Repeat a Number n Times
below are the scenarios using oracle hierarchy query's (ex:-START WITH and CONNECT BY, Level etc.,)
Scenario:-
TABLE TBl
VALUE
|
CNT
|
foo
|
1
|
bar
|
3
|
baz
|
2
|
Expected Output:-
VALUE
|
CNT
|
NDX
|
foo
|
1
|
1
|
baz
|
2
|
1
|
baz
|
2
|
2
|
bar
|
3
|
1
|
bar
|
3
|
2
|
bar
|
3
|
3
|
For Netezza:- use the below method
WITH tbl_populate(value, cnt, ndx)
AS (SELECT value,
cnt,
1
FROM tbl
UNION ALL
SELECT t.value,
t.cnt,
tp.ndx + 1
FROM tbl t
JOIN tbl_populate tp
ON tp.value = t.value
AND tp.ndx + 1 <= t.cnt)
SELECT *
FROM tbl_populate
ORDER BY cnt, ndx
Implementing using Oracle:-
SELECT value,
cnt
FROM tbl p,
(SELECT ROWNUM repeat
FROM dual
CONNECT BY LEVEL <= (SELECT Max(cnt)
FROM tbl))r
WHERE p.tbl >= r.repeat;
Scenario:- I have name like INDIA in a column. I want display 1st line A,
AS (SELECT value,
cnt,
1
FROM tbl
UNION ALL
SELECT t.value,
t.cnt,
tp.ndx + 1
FROM tbl t
JOIN tbl_populate tp
ON tp.value = t.value
AND tp.ndx + 1 <= t.cnt)
SELECT *
FROM tbl_populate
ORDER BY cnt, ndx
Implementing using Oracle:-
SELECT value,
cnt
FROM tbl p,
(SELECT ROWNUM repeat
FROM dual
CONNECT BY LEVEL <= (SELECT Max(cnt)
FROM tbl))r
WHERE p.tbl >= r.repeat;
Scenario:- I have name like INDIA in a column. I want display 1st line A,
2nd line N, 3rd line D, 4th line I, 5th line A in oracle data
base?
Solution:-
SELECT Substr('INDIA', LEVEL, 1) AS INDIA
FROM dual
CONNECT BY LEVEL <= Length('INDIA');
Scenario:-
Source data
ID NAME
101 X,Y,Z
102 XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG
Desired output:-
101 X
101 Y
101 Z
102 XXX
102 Y
102 ZZ
102 AAAAA
102 B
102 CCC
102 D till the end.....
Oracle Solution 1:-
SELECT NAME ,
regexp_substr(NAME, '[^,]+', 1, LEVEL) AS single_element ,
LEVEL AS element_no
FROM (
SELECT ROWNUM AS id1 ,
NAME
FROM TABLE )
CONNECT BY instr(NAME, ',', 1, LEVEL-1) > 0
AND id1 = PRIOR id
AND PRIOR dbms_random.value IS NOT NULL;
Oracle Solution 2:-
SELECT NAME AS original_string ,
extractvalue( xt.column_value, 'element' ) AS single_element
FROM t ,
TABLE( xmlsequence( extract( xmltype( ''
|| replace( t.str, ',', '' )
|| '' ), '/coll/element' ) ) ) xt;
1 comment :
Hi Vamshi,
I tried using this with caluse and it doesnot work with Union all option. Same query i tried running in my system and it did not work. Is it that i am missing something?
Thanks,
Jahnavi
Post a Comment