1. String Concatenation Below are the scripts that will convert multiple records into single record. e.g. concatenate all the employee name based on their deptno separated by ',' as shown below:
DEPTNO AGG_ENAME 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 20 ADAMS,FORD,JONES,SCOTT,SMITH 10 CLARK,KING,MILLER
Method 1. with data as (select ename,deptno, row_number () over (partition by deptno order by ename) rn, count (*) over (partition by deptno order by ename) cnt
from emp
) select deptno, max (ltrim (sys_connect_by_path (ename, ','), ',')) agg_ename from data group by deptno start with rn = 1 connect by prior rn = rn - 1 and deptno = prior deptno ; Method 2. select deptno, ltrim(max(sys_connect_by_path(ename,',')) keep (dense_rank last order by curr),',') as employees from (select deptno,ename, row_number() over (partition by deptno order by ename) as curr, row_number() over (partition by deptno order by ename) -1 as prev from emp) group by deptno connect by prev = prior curr and deptno = prior deptno start with curr = 1; Method 3. create or replace function get_stringagg(p_id in number) return varchar2 as l_str varchar2(2000) default null; l_sep varchar2(1) default null; begin for i in (select text from example2 where id=p_id) loop l_str:= l_strl_sepi.text; l_sep:= ','; end loop; return l_str; end;
2. Spliting String
Reverse of above can be achieved as mentioned below.
First create a test table emp1 to store the above result as below:
create table emp1 as with data as (select ename,deptno, row_number () over (partition by deptno order by ename) rn, count (*) over (partition by deptno order by ename) cnt from emp
) select deptno, max (ltrim (sys_connect_by_path (ename, ','), ',')) agg_ename from data group by deptno start with rn = 1 connect by prior rn = rn - 1 and deptno = prior deptno ;
Then query emp1 as:
select deptno, ename from ( select deptno, trim(substr(str, instr(str, ',', 1, level) + 1,instr(str, ',', 1, level + 1) - instr(str, ',', 1, level) - 1)) ename, level lv, lag(level, 1, 0) over (partition by deptno order by level) lg from (select deptno, ','agg_ename',' str from emp1) connect by instr(str, ',', 1, level) > 0 ) where ename is not null and lv != lg;
3. Count Bad Characters in a String Below query will give non-zero value if bad characters are there else 0. SELECT COALESCE (LENGTH (TRANSLATE ('St Louis', '.' 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' 'abcdefghijklmnopqrstuvwxyz' '1234567890' ' ', '.' ) ), 0 ) "# bad characters" FROM DUAL;
4. Convert Alphanumeric string to Numeric
SELECT REPLACE( TRANSLATE('1234HELL01233LLL' , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ') ,' ' ,'') FROM DUAL;
5. Pull out digits from a string
select translate('..Af$34@#(}fj.s2347adfAdf%','.' || translate('..Af$34@#(}fj.s2347adfAdf%','.0123456789','.'),'.') from dual;
6. Create Flatten Hierarchy from Parent-Child Data Set
Suppose we have table with parent child relationship and we want to create flatten hierarchy till level12
Table:
pr ch
====== =======
A B
B C
B D
C E
D M
E F
F G
and we want to see data as
LEVEL0 LEVEL1 LEVEL2 LEVEL3 LEVEL4 LEVEL5 ...
A B C E F G
B D M
Method 1:
select lv,
level0,
coalesce(level1,level0) level1,
coalesce(level2,level1) level2,
coalesce(level3,level2,level1) level3 ,
coalesce(level4,level3,level2,level1) level4,
coalesce(level5,level4,level3,level2,level1) level5,
coalesce(level6,level5,level4,level3,level2,level1) level6,
coalesce(level7,level6,level5,level4,level3,level2,level1) level7,
coalesce(level8,level7,level6,level5,level4,level3,level2,level1) level8 ,
coalesce(level9,level8,level7,level6,level5,level4,level3,level2,level1) level9,
coalesce(level10,level9,level8,level7,level6,level5,level4,level3,level2,level1) level10,
coalesce(level11,level10,level9,level8,level7,level6,level5,level4,level3,level2,level1) level11,
coalesce(level12,level11,level10,level9,level8,level7,level6,level5,level4,level3,level2,level1) level12 ,
hr
from
(
select lv,
rn as level0,
min(decode(lv,1,ch,substr(hr,(instr(hr,'\',1,2))+1,((instr(hr,'\',1,3) -instr(hr,'\',1,2))-1)) )) level1,
min(decode(lv,2,ch,substr(hr,(instr(hr,'\',1,3))+1,((instr(hr,'\',1,4) -instr(hr,'\',1,3))-1)) )) level2,
min(decode(lv,3,ch,substr(hr,(instr(hr,'\',1,4))+1,((instr(hr,'\',1,5) -instr(hr,'\',1,4))-1)) )) level3,
min(decode(lv,4,ch,substr(hr,(instr(hr,'\',1,5))+1,((instr(hr,'\',1,6) -instr(hr,'\',1,5))-1)) )) level4,
min(decode(lv,5,ch,substr(hr,(instr(hr,'\',1,6))+1,((instr(hr,'\',1,7) -instr(hr,'\',1,6))-1)) )) level5,
min(decode(lv,6,ch,substr(hr,(instr(hr,'\',1,7))+1,((instr(hr,'\',1,8) -instr(hr,'\',1,7))-1)) )) level6,
min(decode(lv,7,ch,substr(hr,(instr(hr,'\',1,8))+1,((instr(hr,'\',1,9) -instr(hr,'\',1,8))-1)) )) level7,
min(decode(lv,8,ch,substr(hr,(instr(hr,'\',1,9))+1,((instr(hr,'\',1,10) -instr(hr,'\',1,9))-1)) )) level8,
min(decode(lv,9,ch,substr(hr,(instr(hr,'\',1,10))+1,((instr(hr,'\',1,11) -instr(hr,'\',1,10))-1)) )) level9,
min(decode(lv,10,ch,substr(hr,(instr(hr,'\',1,11))+1,((instr(hr,'\',1,12) -instr(hr,'\',1,11))-1)) )) level10,
min(decode(lv,11,ch,substr(hr,(instr(hr,'\',1,12))+1,((instr(hr,'\',1,13) -instr(hr,'\',1,12))-1)) )) level11,
min(decode(lv,12,ch,null )) level12,
hr
from
(
select pr,ch,
connect_by_root pr as rn,
level as lv,
sys_connect_by_path(pr,'\')||'\'||ch as hr
from hier
start with pr = 'Departments'
connect by prior ch = pr
) group by hr,lv,rn
);
Method 2:
select distinct coalesce(l.ch,k.ch,j.ch,i.ch,h.ch,g.ch,f.ch,e.ch,d.ch,c.ch,b.ch,a.ch) as id
, a.pr
, a.ch lv1, a.al lv1_al
, b.ch lv2, b.al lv2_al
, c.ch lv3, c.al lv3_al
, d.ch lv4, d.al lv4_al
, e.ch lv5, e.al lv5_al
, f.ch lv6, f.al lv6_al
, g.ch lv7, g.al lv7_al
, h.ch lv8, h.al lv8_al
, i.ch lv9, i.al lv9_al
, j.ch lv10, j.al lv10_al
, k.ch lv11, k.al lv11_al
, l.ch lv12, l.al lv12_al
from hier a
, hier b
, hier c
, hier d
, hier e
, hier f
, hier g
, hier h
, hier i
, hier j
, hier k
, hier l
where a.ch = b.pr(+)
and b.ch = c.pr(+)
and c.ch = d.pr(+)
and d.ch = e.pr(+)
and e.ch = f.pr(+)
and f.ch = g.pr(+)
and g.ch = h.pr(+)
and h.ch = i.pr(+)
and i.ch = j.pr(+)
and j.ch = k.pr(+)
and k.ch = l.pr(+)
No comments:
Post a Comment