Oracle string aggregation wm_concat

Respected Readers,

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:




India|Uttar Pradesh


USA|New York

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.


About Badal Chowdhary

I am a Software Engineer by profession. I have done SCJP and SCWCD certifications. Like working on cutting edge technologies and frameworks. Driven by challenges and fascinated by technology. I love playing and watching sports: Cricket, Ping Pong, Tennis, Badminton, Racket Ball and Gym.
This entry was posted in Oracle and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s