This post is about an in-built Oracle function that goes a long way in aggregating data. wm_concat function aggregates data from multiple rows into one row. Consider the following sample data in lu_country_state table:
USA| New Jersery
Desired Output (Comma separated state values for each country):
India|Maharashtra,Gujarat,Uttar Pradesh, Kerala
USA|New York, New Jersery, California
The above result can be achieved by following sql using wm_concat function:
select country, wm_concat(state) comma_separated_state_list from lu_country_state group by country
Thanks for reading this post.
P.S. This has been tested on Oracle 10g.