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.

12 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

Fireproof said...

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

Hare Krishna said...

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

Hare Krishna 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

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.

Anonymous said...

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

Unknown said...

Thanx very much!!!

Unknown said...

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

Unknown said...

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

fillikir72518 said...

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