#within group

Oracle impressed me again tonight. Often when I feel that someone must have needed to do 'X' before, I can find some uncommonly used feature in oracle that does 'X' for me.

I had a table which had an identifier, line number and a comments field. All the lines for a specific identifier grouped together represented comments for one item.

My first thought was to use listagg (sorry wm_cat you've been denied). Listagg lets you specify the delimiter. It seems many people I know are unaware of this functionality. I found a function in our codebase that wrapped list agg and ran replace on ',' a week or so. I just shook my head.

Second neat thing about listagg is that you can use the within group functionality with it and specify a group (and order for that group)  before it aggregates. So in this case I want to order by line. So far we've got:

select listagg(comments, ' ') within group (order by line) from sometable;

We can tack on a group by here and it will first group, then list agg across the smaller group (that uses a different column). AWESOME!

The resulting query looks like this:

select id, listagg(comments, ' ') within group (order by line)

from sometable

group by id;