SQL QUERY – Merge data from rows with same ID

Im using wordpress for my website and i have a massive amount of data that i want to export through SQL.
The thing is that I’m receiving for each post(item) several rows with data under the category column. What I’m trying to do is merge all the categories so eventually i will have only 1 row for each post with all the categories.

This is currently my query:

Read More
    SELECT post_title, b.object_id, b.term_taxonomy_id, c.term_id, d.name
FROM wp_posts a
JOIN wp_term_relationships b ON a.ID = b.object_id
JOIN wp_term_taxonomy c ON b.term_taxonomy_id = c.term_taxonomy_id
JOIN wp_terms d ON d.term_id = c.term_id

here is an example of my results:

post_title     object_id  term_taxonomy_id  term_id name
Holcroft Covenant   2       5                 5         Action
Holcroft Covenant   2       6                 6         Drama
Holcroft Covenant   2       8                 8         Thriller

The Result that i would like is:
Holcroft Covenant 2 5 5 Action,Drama,Thriller

Any Idea’s?

Related posts

Leave a Reply

1 comment

  1. If you’re using MySql try this:

    SELECT 
        post_title, b.object_id, b.term_taxonomy_id, c.term_id, GROUP_CONCAT(d.name)
    FROM wp_posts a
        JOIN wp_term_relationships b ON a.ID = b.object_id
        JOIN wp_term_taxonomy c ON b.term_taxonomy_id = c.term_taxonomy_id
        JOIN wp_terms d ON d.term_id = c.term_id
    GROUP BY post_title, b.object_id, b.term_taxonomy_id, c.term_id