How to transpose multiple table rows to a single row in MySQL

I need to use a multi-valued field in a table some days before, where I need to associate a list of tags to webpages, similar to the bookmark manager del.icio.us. The adopted modeling alternative was create a relational table with two fields (webpage-key and tag-key) and map the tags to the correspondent webpage. So, the tags are in a column and one webpage may have more than one tag. How to transpose those tags, to show each webpage and it list of tags in a single line? In MySQL you can use ‘GROUP_CONCAT’:

DROP TABLE IF EXISTS page, tag, page_tag;

CREATE TABLE page(pid INT PRIMARY KEY, url VARCHAR(32) );

CREATE TABLE tag(tid INT PRIMARY KEY, tag VARCHAR(32) );

CREATE TABLE page_tag(pid int, tid varchar(1), PRIMARY KEY pk(pid, tid) );

INSERT INTO page VALUES 
   (1, 'https://lembra.wordpress.com'), 
   (2, 'http://grooveshark.com'), 
   (3, 'http://stackoverflow.com');

INSERT INTO tag VALUES 
   (1, 'blog'), (2, 'music'), (3, 'questions'), (4, 'social-network'), (5, 'rss');

INSERT INTO page_tag VALUES 
   (1, 1), (2, 2), (3, 3), (1, 4), (2, 4), (3, 4), (1, 5);

SELECT url, GROUP_CONCAT(tag) tags 
FROM page p, page_tag r, tag t 
WHERE p.pid = r.pid AND r.tid = t.tid 
GROUP BY url;

-- +-----------------------------+--------------------------+
-- | url                         | tags                     |
-- +-----------------------------+--------------------------+
-- | http://grooveshark.com      | music,social-network     |
-- | https://lembra.wordpress.com | blog,social-network,rss  |
-- | http://stackoverflow.com    | questions,social-network |
-- +-----------------------------+--------------------------+
-- 3 rows in set (0.00 sec)

See ya!

2 Responses to How to transpose multiple table rows to a single row in MySQL

  1. Pingback: How to transpose a single table row to multiple rows in MySQL « Remember's Blog

  2. Kiran says:

    Thanks a lot.
    This is what I was actually looking for.
    I didn’t know that MySql has group_concat method.

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

%d bloggers like this: