selecting values dynamic from columns using pivoting mysql

Question

I have a brands table and posts table.

Below is their schema

Brands :

brands_id, friendly, short ,followers, created, location, brandID, handle, url, pic, utcOffset, posts, engagements, engagedUser, etc(sufficient for current question)

Posts :

post_id, brandID, postID, handle, posted, content, postType, comments, etc (sufficient for current question)

where postType= link, status,video,question,poll,etc

Now I have hardcoded pivoting with the following query:

select b.friendly,
sum(case when p.postType='link' then 1 else 0 end)  as 'link',
sum(case when p.postType='video' then 1 else 0 end)  as 'video',
sum(case when p.postType='status' then 1 else 0 end)  as 'status',
sum(case when p.postType='photo' then 1 else 0 end)  as 'photo',
count(p.postType) 
from brands b, posts p 
where b.handle 
in ('chevroletcanada','dodgecanada') 
    and p.handle=b.handle
    and date(p.posted) 
BETWEEN "2013-06-02" and "2013-08-11" 
group by b.friendly

But in the above query I have used types of postType statically, i.e for links, status, video, photo. Now if a new postType is added to the posts table, this won't work, as I would have to change the query too. Also if existing values in postType is deleted, then too the query have to be changed again.

My question is how can I achieve this dynamically so that when new postType values for instance tweet is added in posts table then tweet will show up in the result sets as well. And the same for deletion of any postType.

If you haven't understood question, please inform me.

I have read the below posts, but unable to figure out:

MySQL or PHP Turning rows into columns dynamically

MySQL pivot table query with dynamic columns

Thanks in advance!!

@TomMac No problem. It's fine if I get this done using subqueries or joins — sizzyfrisky, Oct 25 '13 at 10:47
Just assemble a GROUPed and ORDERed result and handle the display logic at the application level (e.g. a bit of PHP). You can also use a sproc but I don't like that idea. — Strawberry, Oct 25 '13 at 11:41

sizzyfrisky · Accepted Answer · 2013-10-30T11:47:26.703

This is the solution for the above problem. Dynamic pivot can be achieved like this:

SET @sql = NULL;

  SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when postType = ''',
      postType,
      ''' then 1 end) as `', 
      postType, '`')
  ) INTO @sql
FROM posts; 
SET @sql = CONCAT('SELECT b.friendly, count(p.postType), ', @sql, ' from brands b, posts p where b.handle in ("dodgecanada","chevroletcanada") and p.handle=b.handle
and date(p.posted) BETWEEN "2004-08-11" and "2013-09-11" group by b.friendly');

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

The above query displays right results in SQL interface HeidiSQL. But returns: "#1243 - Unknown prepared statement handler (stmt) given to EXECUTE" message in phpMyAdmin.

Can anyone tell me why is this so? Is 'prepared' statement not supported in Mysql? or is there any issue in phpMyAdmin running prepared statements. How can I overcome this in phpMyAdmin?And can anyone tell me how can I do this in PHP? Thanks!!

selecting values dynamic from columns using pivoting mysql

1 Answers1