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.
10 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
welcome to the wow power leveling cheap Wow gold service site, buy cheap wow gold,wow gold,world of warcraft power leveling buy wow gold
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
Christian Louboutin
As a way to design and style remarkably one of a kind and effective Christian Louboutin Petal Sandals; they consider benefit of your very last equipment and technologies in order that you’ll be fully capable of get the brightest and designer sandal designs from them in way. Seems wise, the Christian Louboutin Petal Sandals are extremely inimitable and flexible shoes in an attempt to seize your attentions directly. On the subject of the colours, the monumental fascination is that they use superlative colors for making fancy your Christian Louboutin Petal Sandals.
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
Post a Comment