Oracle ListAgg within group functionality
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;
Nifty, eh?