Oracle Group_Concat

Problem: I needed a query in Oracle that performs aggregate functions on strings similar to how SUM() is an aggregate function for numbers.

MySQL has the group_concat() function but Oracle does not have an equivalent.

Solution: I wrote a PL/SQL function called grp_concat() similar to this one

take the following data:

LAKE REASONS NOT STOCKED
62000200 High NOP Pop.
62005700 High NOP Pop.
27003100 High NOP Pop.
10012100 High NOP Pop.
27001900 High NOP Pop.
70009100 High NOP Pop.
82005200 Low Forage
02000600 Low Forage
27001900 Low Forage
70007200 Low Forage
27011101 Low Forage
82005400 Low Forage
10005900 Poor Growth

I need Lake 27001900 to be reported as ” High NOP pop , Low Forage

and I can do this with
Select LAKE_ID, GRP_CONCAT(LAKE_ID) from MY_TABLE

This has the disadvantage of being specific to one table while group_concat() can work with any query. It has the advantage of cleaning up the SQL code in the report as I can just use the line
GRP_CONCAT(A.WATER_DOW) as Reasons

Instead of grabbing two more tables and joining them in my SQL statement. The query in use already has 4 tables.

Related Posts

No related posts.

About Tim

Minneapolis Blogger
This entry was posted in General. Bookmark the permalink.

Comments are closed.