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:

Country|State

India|Maharashtra

India|Gujarat

India|Uttar Pradesh

India|Kerala

USA|New York

USA| New Jersery

USA|California

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.

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s