MySQL basic table pivoting

Question

I'm trying to pivot a table result that looks like.....

     -----------------------------------------------
    | personsID    |   personsWeight |  dateWeighed |
     -----------------------------------------------
    |          1   |      160        |   04/15/2011 |
     -----------------------------------------------
    |          1   |      161        |   04/18/2011 |
     -----------------------------------------------
    |          1   |      162        |   06/30/2011 |
     -----------------------------------------------
    |          1   |      163        |   07/11/2011 |
     -----------------------------------------------
    |          1   |      164        |   04/01/2012 |
     -----------------------------------------------

I want it to look like

     --------------------------------------------------------------------------------
    |  personsID   | 04/15/2011  | 04/18/2011 | 06/30/2011 | 07/11/2011 | 04/01/2012 |
     --------------------------------------------------------------------------------
    |        1     |     160     |   161      |     162    |     163    |    164     |
     --------------------------------------------------------------------------------

This is not a duplicate question. I've seen lots of examples where the column's are manually written (eg jan, feb,......, dec). I'm having a really hard time finding out how to generate the column names based on the values given in the original table. — user2125899, Apr 20 '13 at 19:49

score 1 · Accepted Answer · answered Apr 20 '13 at 20:32

MySQL does not have a PIVOT function so you will have to use an aggregate function with a CASE expression to transform the rows of data into columns.

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

select personsId,
  sum(case when dateWeighed='2011-04-15' then personsWeight else 0 end) `04/15/2011`,
  sum(case when dateWeighed='2011-04-18' then personsWeight else 0 end) `04/18/2011`,
  sum(case when dateWeighed='2011-06-30' then personsWeight else 0 end) `06/30/2011`
from yt
group by personsId;

See SQL Fiddle with Demo.

But if you have an unknown number of dates that you want to turn into columns, then you will want to use a prepared statement to generate dynamic SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN dateWeighed = ''',
      dateWeighed,
      ''' THEN personsWeight else 0 END) AS `',
      date_format(dateWeighed, '%m/%d/%Y'), '`'
    )
  ) INTO @sql
FROM yt;

SET @sql 
  = CONCAT('SELECT personsId, ', @sql, ' 
            from yt
            group by personsId');

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

See SQL Fiddle with Demo. Both will give the result:

| PERSONSID | 04/15/2011 | 04/18/2011 | 06/30/2011 | 07/11/2011 | 04/01/2012 |
------------------------------------------------------------------------------
|         1 |        160 |        161 |        162 |        163 |        164 |

Thanks a lot. I was hoping the answer wouldn't be quite so difficult but thank you very much for taking the time to address it. — user2125899, Apr 20 '13 at 23:46

MySQL basic table pivoting

1 Answers1