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
select job,
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

--------- ----------------------------------------

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

Full TK post over here.


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

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.

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:

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!!!

Whatsapp Descargar said...

Thank you for sharing valuable information. Nice post. I enjoyed reading this post.
download free descargar whatsapp and download baixar whatsapp online and descargar whatsapp gratis , baixar whatsapp gratis

Frozen Juegos said...

Hello, just wanted to say, I loved this article. It was practical.
Keep on posting!
Jugar juegos de frozen en línea gratis, los nuevos de princesa de Disney juegos frozen - la princesa encantadora y linda. Divertirse frozen!

Facebook Descargar said...

Thank you for that information you article
download descargar facebook gratis para Android celular and download free descargar facebook apk and descargar facebook gratis , descarga facebook

Anh Mai said...

I love all the posts, I really enjoyed, I would like more information about this, because it is very nice., Thanks for sharing.
Versión en facebook en español descargar a los países hablan Español: facebook entrar direto agora , facebook en español para and facebook entrar direto

Mít Trang 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.
juegos de matar zombies
jogos do friv

games unblocked 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