Pivot Table Using MySQL

Question

I have two tables Triples and Tags

Triples Table has the following Columns

  id  PostID  TagID   Value
   1   1        1     Murder
   2   1        2     New Brunswick
   3   2        1     Theft
   4   2        3     Gun

Tags Table has the following Columns

  id   TagName
   1   Incident
   2   Location
   3   Weapon

I am trying to write sql to create a Pivot Table with Dynamic Headers

Output should be like this

       PostID  Incident   Location         Weapon        
           1   Murder     New Brunswick    
           2   Theft                        Gun

Any help in writing the SQL would be appreciated. I have seen examples online but could not figure out this one

SQLFiddle (sqlfiddle.com) is a great resource for testing ideas. — Richard Brown
– Richard Brown, Commented Mar 19, 2013 at 21:21
possible duplicate of MySQL pivot row into dynamic number of columns — RichardTheKiwi
– RichardTheKiwi, Commented May 3, 2013 at 9:16

Taryn · Accepted Answer · 2013-03-19 21:28:57Z

In order to pivot the data in MySQL, you will need to use both an aggregate function and a CASE expression.

If you have a known number of columns, then you can hard-code the query:

select p.postid,
  max(case when t.tagname = 'Incident' then p.value end) Incident,
  max(case when t.tagname = 'Location' then p.value end) Location,
  max(case when t.tagname = 'Weapon' then p.value end) Weapon
from triples p
left join tags t
  on p.tagid = t.id
group by p.postid;

See SQL Fiddle with Demo

But if you have an unknown number of columns, then you will need to use a prepared statement to generate dynamic SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN TagName = ''',
      TagName,
      ''' THEN p.value END) AS `',
      TagName, '`'
    )
  ) INTO @sql
FROM tags;


SET @sql 
  = CONCAT('SELECT p.postid, ', @sql, ' 
           from triples p
           left join tags t
             on p.tagid = t.id
           group by p.postid');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo.

Both will give the result:

| POSTID | INCIDENT |      LOCATION | WEAPON |
----------------------------------------------
|      1 |   Murder | New Brunswick | (null) |
|      2 |    Theft |        (null) |    Gun |

Collectives™ on Stack Overflow

Pivot Table Using MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related