We found a quite good solution a while back and after checking Tom Kytes blog I found a even better solution today.
This is mighty impressive SQL code, very simple functions but used in a very clever way.
Have a peek.
with data
as
(
select job,
ename,
row_number() over (partition by job order by ename) rn,
count(*) over (partition by job) cnt
from emp
)
select job, ltrim(sys_connect_by_path(ename,','),',') scbp
from data
where rn = cnt
start with rn = 1
connect by prior job = job and prior rn = rn-1
order by job
/
JOB SCBP
--------- ----------------------------------------
ANALYST FORD,SCOTT
CLERK ADAMS,JAMES,MILLER,SMITH
MANAGER BLAKE,CLARK,JONES
PRESIDENT KING
SALESMAN ALLEN,MARTIN,TURNER,WARDHow cool is that, nothing extra to the query! Just beautiful use of the analytical functions.
Full TK post over here.
12 comments:
This can also be done in a similar way using the MODEL clause (available in Oracle 10g or greater). In this example, I'm concatenating without any separator. The update rule in the model can easily be changed accordingly.
with data as
(
select job, ename,
row_number() over (partition by job order by ename) rn,
count(*) over (partition by job) cnt
from emp
)
select job, scbp
from (
select job, scbp, rn, cnt
from data
model
partition by (job)
dimension by (rn)
measures (ename, cnt, cast(null as varchar2(50)) scbp)
(
scbp[any] = scbp[cv()-1] || ename[cv()]
)
)
where rn = cnt
order by job
/
JOB SCBP
--------- --------------------------------------------------
ANALYST FORDSCOTT
CLERK ADAMSJAMESMILLERSMITH
MANAGER BLAKECLARKJONES
PRESIDENT KING
SALESMAN ALLENMARTINTURNERWARD
This post would be much more useful if you shared with us the table structure being used.
in 10 g we can use wmsys.wm_concat function for this
select job,wmsys.wm_concat(ename) from scott.emp group by job
Actually wm_concat is an undocumented function. I guess it would be better to use listagg.
http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions087.htm
I found a nice article in this page below:
http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php
I hope this help you.
I enjoy so much this kind of articles because I can put it on practice and learn
Generic Viagra
Thanx very much!!!
What a great online source of information about this topic. you have done great work. keep continue to sharing such kinds of post. keep it up.
juegosjuegos.com
juegos de matar zombies
jogos do friv
This is also a very good post which I really enjoyed reading. It is not everyday that I have the possibility to see something
kids games online
friv 2
unblocked games
juegos de un show mas
Youre so cool! I dont suppose Ive read anything like this before. So nice to find somebody with some authentic ideas on this subject. realy thanks for beginning this up. this website is one thing that is needed on the internet, somebody with a little bit originality. useful job for bringing something new to the internet! online casino
Post a Comment