How to concatenate field values based on a "GROUP BY" Field - How to use MySQL GROUP_CONCAT() function
How to use MySQL GROUP_CONCAT() function
How to concatenate field values based on a "GROUP BY" Field
Let's understand this with an example
There is a table "tbl_Locations" having 2 fields Location and State
Task is to get the Locations under a state as a comma separated string
ie, to show as given below
You can do this by using the MySQL GROUP_CONCAT() function
SYNTAX:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
GROUP_CONCAT function concatenates values within each group defined by GROUP BY clause.
MySQL QUERY:
SELECT State, GROUP_CONCAT(Location) as Locations FROM tbl_Locations GROUP BY State;
Result:
Reference : MySQL GROUP_CONCAT()
Power of Group_Concat
How to concatenate field values based on a "GROUP BY" Field
Let's understand this with an example
There is a table "tbl_Locations" having 2 fields Location and State
| Location | State |
| Kochi | KL |
| Alleppy | KL |
| Chennai | TN |
| Coimbatore | TN |
| Trivandrum | KL |
Task is to get the Locations under a state as a comma separated string
ie, to show as given below
| State | Locations |
| KL | Kochi,Alleppy,Trivandrum |
| TN | Chennai,Coimbatore |
You can do this by using the MySQL GROUP_CONCAT() function
SYNTAX:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
GROUP_CONCAT function concatenates values within each group defined by GROUP BY clause.
MySQL QUERY:
SELECT State, GROUP_CONCAT(Location) as Locations FROM tbl_Locations GROUP BY State;
Result:
| State | Locations |
| KL | Kochi,Alleppy,Trivandrum |
| TN | Chennai,Coimbatore |
Reference : MySQL GROUP_CONCAT()
Power of Group_Concat
