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.|
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.
No related posts.