Pivot Table Using MySQL and Group Conact

Question

This is a Question related to my original question

Pivot Table Using MySQL

Data in the tables are saved like this now

I have two tables Triples and Tags

Triples Table has the following Columns

  id  PostID  TagID   Value
   1   1        1     Murder
   2   1        1     Theft
   3   2        2     Knife 
   4   2        2     Gun 

Tags Table has the following Columns

 id   TagName
  1   Incident
  2   Weapon

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

Output should be like this

   PostID  Incident              Weapon        
       1   Murder,Theft        
       2                          Knifie,Gun 

I written a inefficient and partial sql query for this, Any help in writing this sql appreciated

   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('CREATE VIEW PostTags as SELECT p.postid, p.id, ', @sql, ' 
          from triples p
          left join tags t
            on p.tagid = t.id
            group by p.postid, p.id');

         PREPARE stmt FROM @sql;

     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;

Answer 1

You will have to build two strings, one for the inner rotation to create the columns of Incident and Weapon, and the second string with use GROUP_CONCAT for the outer query:

SET @sql = NULL;
SET @sql2 = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
            CONCAT(
             'group_concat(',
              TagName,
              ' separator '', '') as `',
               TagName, '`'
               )
    ) 
INTO @sql2
FROM tags;

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

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

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

See SQL Fiddle with Demo

This is creating the SQL code:

select postId,
  group_concat(incident separator ', ') incident,
  group_concat(Weapon separator ', ') Weapon
from
(
  SELECT p.postid, p.id, 
    max(CASE WHEN TagName = 'Incident' THEN p.value END) AS `Incident`,
    max(CASE WHEN TagName = 'Weapon' THEN p.value END) AS `Weapon` 
  from triples p 
  left join tags t 
    on p.tagid = t.id 
  group by p.postid, p.id
) d
group by postId;

See SQL Fiddle with Demo