If you are implementing a tagging system like what you see with WordPress, then you may have a requirement to fetch all the tags of each article in the blog when displaying the blog. As each article has many tags, one option is to first fetch all the articles and for each article fetch the list of tags. However, there is a better way to do this in one single SQL when using MySQL.
MySQL has the function group_concat that allows one to condense a set of rows into one single row by concatenating the list of values.
The SQL will be something like
select article_id,article_title,article_text,(select group_concat(tag) from tags t where t.article_id = a.id) tags from articles a …;
Here, the subselect used for the tags column is what I am talking about. It uses the group_concat to concatenate all the tags together. By default it uses ‘,’ as the separator and I believe that can be altered.
PHP & MySQL Web Development is a book for learning web development with PHP and MySQL.
poeticcode 
Great post, this is exactly what I was looking for. Handy little feature, I’m sure I’ll be using it regularly now.
Thanks for the tip! I needed to create an airport route for a reservation program I’m writing
If you are wondering how to do this in Oracle, here are a few techniques
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
User-Defined Aggregate Function approach is perhaps the best approach.
This is amazing 🙂 I’ve been doing this using stored functions in MySQL 5.
Great thank u.
Great article. I was searching for something like this.
Thank You!
Could anyone guide me how to do this in MSSQL.
Thanks,
David.
Thanks,
Cesar,
thanks!
Great! That’s exactly what I needed!
Thousand Thanks!
Jan
Thanks a lot…………. It solves my problem…
Great Post! That’s exactly what i needed!
Thanks Buddy
Keep Rocking
can you give an example?
Anybody knows how to do the same with DB2..Pls respond
Do not forget to cast if you’re dealing with integer values like this one:
…, (SELECT GROUP_CONCAT(CAST(id as CHAR)) FROM your_table WHERE col = something) AS col_name, …
Old post but helpful. It was the first to come up when I was looking for this sort of functionality.
For others new to it, also note that there is a limit to the string created. By default it is 1024. Adding
SET group_concat_max_len := @@max_allowed_packet;
or some other value is quite helpful.
Thanks much!
What an excellent function.
I have to do a report next week in T-SQL, i really hope that Microsoft have something like this.
Thanks for the post
Thanks. It help me a lot. 🙂
I will use this always.
This post saved me some work – thanks! 🙂
BTW, if you want to concatenate with a delimiter you can use like this
group_concat(name separator “|”)
if someone’s looking for it.