Friday, August 4

Oracle group_concat() updated (again)

I've ranted about this twice before. The mysql group_concat() equivalent in Oracle has in the past been quite problematic query.
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,WARD

How cool is that, nothing extra to the query! Just beautiful use of the analytical functions.

Full TK post over here.

11 comments:

Anonymous said...

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

uiyui said...

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

Everett said...

This post would be much more useful if you shared with us the table structure being used.

Prakash said...

in 10 g we can use wmsys.wm_concat function for this

Prakash said...

select job,wmsys.wm_concat(ename) from scott.emp group by job

Ravi Sanwal said...

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

highheels said...

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.

Eric Silva said...
This comment has been removed by the author.
Eric Silva said...

I found a nice article in this page below:

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

I hope this help you.

Andy said...

I enjoy so much this kind of articles because I can put it on practice and learn
Generic Viagra

Valentina Anisimova said...

Thanx very much!!!