MySQL: String concat multiple rows

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.

Advertisements

20 Comments

Filed under MySQL, tagging

20 responses to “MySQL: String concat multiple rows

  1. craigb

    Great post, this is exactly what I was looking for. Handy little feature, I’m sure I’ll be using it regularly now.

  2. Thanks for the tip! I needed to create an airport route for a reservation program I’m writing

  3. S

    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.

  4. fieldeffect

    This is amazing ๐Ÿ™‚ I’ve been doing this using stored functions in MySQL 5.

  5. satyac46

    Great thank u.

  6. Asok

    Great article. I was searching for something like this.

    Thank You!

  7. David

    Could anyone guide me how to do this in MSSQL.
    Thanks,
    David.

  8. digitalmusiccollector

    Thanks,
    Cesar,

  9. beathovn

    Great! That’s exactly what I needed!
    Thousand Thanks!

    Jan

  10. Yhanzkie

    Thanks a lot…………. It solves my problem…

  11. Suriya

    Great Post! That’s exactly what i needed!

    Thanks Buddy

    Keep Rocking

  12. can you give an example?

  13. Naveen

    Anybody knows how to do the same with DB2..Pls respond

  14. 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, …

  15. 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!

  16. 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

  17. colene

    Thanks. It help me a lot. ๐Ÿ™‚

    I will use this always.

  18. Hannes

    This post saved me some work – thanks! ๐Ÿ™‚

  19. BTW, if you want to concatenate with a delimiter you can use like this

    group_concat(name separator “|”)

    if someone’s looking for it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s