Jump to content
Changes to the Jaspersoft community edition download ×

How to convert values in certain colum to comma separated list?


report_generator

Recommended Posts

  • Replies 1
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

In your query you can use the GROUP_CONCAT function and GROUP BY the field you don't want duplicated to achieve that result. Here's an example table with duplicated data:

 

StateCity
New YorkNew York City
New YorkSyracuse
CaliforniaLos Angeles
CaliforniaSan Francisco
TexasAustin

 

With the below query

SELECT state, GROUP_CONCAT(city SEPARATOR ", ") AS cities FROM table GROUP BY state[/code]

The data will now be displayed as

statecities
New YorkNew York City, Syracuse
CaliforniaLos Angeles, San Francisco
TexasAustin

 

You can also sort the cities or remove duplicates from that field as well with additional functions inside the GROUP_CONCAT. More information about GROUP_CONCAT can be found at https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat

 

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...