Saturday , 7 December 2019
Breaking News
Home > Tutorials > MySQL > How to Concatenate Single Column Values with MySql

How to Concatenate Single Column Values with MySql

Today we will solve a problem related to MySQL query. Here is a problem:-
Let’s suppose you have a table named “Items” and it has three fields and following data:-

Id Item_name Price
1 Item 1 10
2 Item 2 20
3 Item 3 30

Now you want to write a query that will get all values from “Items” column and concatenate it putting a comma between every item name like this “Item 1, Item 2, Item 3”. You often need this type of data when you want to generate graph/chart. In the normal query you will write something like this:

And it will produce this type of data

Item_name
Item 1
Item 2
Item 3

But it is not our demand we need this kind of data “Item 1, Item 2, Item 3”. So now what will you do when you received this data. You surely write some PHP function that will grab every item and then save it in an array and then put a comma and then get next item name and so on. I think this approach is not good. I have a solution for you. There is a function in MySQL named “GROUP_CONCAT”. This function will grab data from the column and will generate the concatenated result. Let’s see in action. Write this query:-

and it will generate this type of data “Item 1,Item 2,Item 3” (without quotes). But wait a minute, this function does more for you. It has 2nd parameter where you will set separate for your data, see this query:-

and it is generate this kind of data “Item 1+Item 2+Item 3”. The second parameter will tell this query to put “+” as a SEPARATOR. You can write any symbol in place of “+” in above example.
Good luck and Happy Coding.

About Muhammad Faryad

Muhammad Faryad is a professional web application developer. He has been working for 5 years in ICE786 Technologies Pvt. Ltd as a Senior Programmer and Developer. You can contact him on facebook at www.facebook.com/tolamangali

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Ad Test