GROUP_CONCAT() function is used to concatenate column values into a single string. It is very useful if you would otherwise perform a lookup of many row and then concatenate them on the client end.
For example if you query:
mysql> SELECT Language FROM CountryLanguage WHERE CountryCode = 'THA';
It outputs:
Language |
Chinese |
Khmer |
Kuy |
Lao |
To concatenate the values into a single string, you query:
mysql> SELECT GROUP_CONCAT(Language) As Languages FROM CountryLanguage WHERE CountryCode = 'THA';
Then the output will be:
Languages |
Chinese, Khmer, Kuy, Lao |
You can also use some format of GROUP_CONCAT(). Like
- SELECT GROUP_CONCAT( Language SEPARATOR ‘-‘ )… It will use ‘-‘ instead of ‘,’
- SELECT GROUP_CONCAT( Language ORDER BY Language DESC )… To change the order and shorting output
One thing to remember: GROUP_CONCAT() ignores NULL values.
October 20, 2008 at 5:50 am
update admin_messages
set subject=’function’
where messageId = (select max(messageId) from admin_messages)
this query is create error plz. solve this query solution
regards
Parveen Sharma
March 4, 2011 at 10:35 am
It is not possible to update the same table and use that in the from clause in your subquery. It could be rewritten using joins
October 20, 2008 at 6:15 am
possible more than row result in this format example
name marks
abc 36
xyz 56
def 76
abc 86
abc 66
def 76
Result is
abc xyz def
36 56 76
86 76
66
regards
Parveen Sharma
August 16, 2012 at 9:30 pm
Did you ever get a query to accomplish this? I’m looking to do something quite similar and am unsure how to do it.
October 25, 2009 at 8:44 pm
I think group_concat() has actually different purpose:
e.g. To group or show the people who lives in same address, or show the students who have same score.
query : select student, group_concat(score) from student group by student_name;
and if you want to group which has more than one people, the query can be:
select student, group_concat(score) from student group by student_name having count(student_name) > 1;
I need this email for one of my projects.
Happy to share here 🙂
November 16, 2009 at 4:42 pm
Thanks for the write up, very helpful.
April 22, 2010 at 11:50 pm
Thanks a ton!! This is exactly what I was looking for!
November 30, 2010 at 12:23 pm
Thanks man
August 20, 2011 at 5:21 am
This was useful, thanks! In addition to the above examples, you can use the DISTINCT keyword to get only unique values within the GROUP_CONCAT. e.g. GROUP_CONCAT(DISTINCT Languages)
September 9, 2011 at 10:00 pm
thanks much this is most enlightening Parveen,
is there a function that does the reverse of group_concat
ie. from languages: english,french,chinese
to generate separate rows as follows:
english
french
chinese
September 9, 2011 at 10:58 pm
reverse group_concat in order to resolve the following:
table1 project=A lang=chinese,french,english,spanish
table2 lang texte
==== ====
spanish ola
french bonjour
german hallo
i need to do a join of the 2 tables to obtain only the records of table2 based on the languages in table1 from record project=A which has lang=chinese,french,english
such that the result would be
lang texte
==== ====
french bonjour
spanish ola
thanks much,
October 8, 2011 at 12:40 am
Wonderfully useful.
I just used this to create a simple function for looking up users based on profile information in Drupal 6.
see: http://regx.dgswa.com/html/content/search-drupal-user-profiles
May 7, 2012 at 3:29 pm
I had been really glad to learn your internet site, it features specifically the content I’m planning to locate.
May 10, 2012 at 9:35 am
Thanks useful information
May 11, 2012 at 10:36 am
False Hairpiece http://synthetic-wigs.dolabuy.com/ Com their wide selection of make Synthetic Flowing hair Hair pieces, Particularly long Imitation Wigs, Reasonably priced Fake Wigs, Fast Fabricated Wigs, Internal Fabricated Hairpiece and
May 26, 2012 at 1:23 am
keren. terima kasih..
July 5, 2012 at 7:05 am
Grete Post very Helpful function
December 3, 2012 at 2:11 pm
Thanks, Very easy to understand via example.
December 13, 2012 at 9:02 am
Thanks Qiu…
very usefull…
March 9, 2013 at 1:41 pm
After exploring a number of the blog posts on your website, I seriously like your technique of
writing a blog. I saved it to my bookmark webpage list and will be checking back soon.
Please visit my website too and tell me what you think.
March 24, 2013 at 10:24 pm
I really desired to discuss this particular post,
Roman Shades “MySQL – The GROUP_CONCAT() function | Think Different” together with my own pals on facebook itself.
Ijust simply planned to disperse ur terrific writing!
Many thanks, Geneva
April 7, 2013 at 5:43 pm
Someone necessarily assist to make critically articles I would state.
This is the first time I frequented your website page and to this point?
I amazed with the analysis you made to create this actual
post incredible. Wonderful job!
April 30, 2013 at 5:02 am
I’m very happy to find this page. I need to to thank you for your time due to this wonderful read!! I definitely enjoyed every little bit of it and I have you bookmarked to see new things on your site.
June 18, 2013 at 3:05 pm
Great post. I was checking constantly this weblog and I am impressed!
Very helpful info specially the final phase 🙂 I take care of such information much.
I used to be seeking this certain info for a very long time.
Thank you and good luck.
July 1, 2013 at 9:32 pm
“MySQL – The GROUP_CONCAT() function | Think Different” was a superb
blog post, can’t help but wait to read through more of ur posts.
Time to waste numerous time on-line lol. Thanks for your effort ,Victorina
July 9, 2013 at 10:34 pm
This particular blog, “MySQL – The GROUP_CONCAT() function |
Think Different” was in fact outstanding.
I am printing out a duplicate to demonstrate to my close friends.
Thanks for the post-Reyes
July 12, 2013 at 7:05 am
Greate!!
August 13, 2013 at 5:38 pm
Excellent post. I was checking continuously this weblog and I’m impressed! Very helpful information particularly the ultimate section 🙂 I care for such info much. I used to be looking for this certain information for a very lengthy time. Thank you and best of luck.
November 11, 2013 at 1:32 pm
How can I add a hyperlink to the comma separated values? Imagine I use as the separator can i use those as individual hyperlinks?
December 4, 2013 at 6:19 am
test
December 31, 2013 at 3:14 pm
Hi there very nice site!! Man .. Excellent ..
Wonderful .. I’ll bookmark your web site and
take the feeds also? I’m satisfied to find a lot of useful information here in the post, we’d like develop more strategies
on this regard, thanks for sharing. . . .
. .
July 18, 2014 at 9:01 am
nice
July 18, 2014 at 9:01 am
luking good
July 18, 2014 at 9:02 am
July 18, 2014 at 9:03 am
test
April 8, 2015 at 10:55 am
What i don’t understood is in fact how you’re now
not really a lot more smartly-preferred than you might be now.
You are very intelligent. You realize therefore considerably with regards to
this subject, produced me in my opinion believe it from numerous varied angles.
Its like women and men don’t seem to be fascinated until it is something to accomplish with Woman gaga!
Your individual stuffs great. Always maintain it up!
April 25, 2016 at 1:19 pm
Hello,
I am having problem to search data from two tables where both fields have comma separated values in different order.
For instance, I have two tables:
1. Users
id gender ethnicity
1 male asian,american,african
2 female asian,african,american
3 female any
2. Roles
id ethnicity
25 american,asian,african
102 african,american,asian
451 any
402 any,pecific islander
Now, I want to fetch data from both the tables on the basis of “ethnicity” field. Condition is, at least one value should match from both the fields. Here after comparing both the tables there may be multiple records we may have but I need one record for same user and same role. If same user will have same role and will have multiple records then we need one of all records.
I did R&D but didn’t find anything for this case. So please help me out for this and give me your best solution for this as soon as possible. I hope I will get a solutions from your side for sure.
Thanks in Advance.. 🙂