Pivot table Mysql

Question

I have a table:

  | ID | PROJECT_ID |  MONTH |MEMBER_ID|
  +------------------------------------+
  |  1 |          1 |      1 |       1 |
  |  2 |          1 |      1 |       2 |
  |  3 |          1 |      2 |       1 |
  |  4 |          1 |      2 |       2 | 
  |  5 |          1 |      3 |       1 |
  |  6 |          2 |      1 |       3 |
  |  7 |          2 |      2 |       3 |
  |  8 |          2 |      3 |       2 |
  |  7 |          2 |      3 |       3 |

I would like to create a table like this:

| PROJECT_ID |  TITLE | MONTH_1 | MONTH_2 | MONTH_3 |
+---------------------------------------------------+
|          1 | title1 |       2 |       2 |       1 |
|          2 | title2 |       1 |       1 |       2 |

In each month I have a total number of members. How can I realize it?

Thanks!

A good place to start would be to swot up on http://dev.mysql.com/doc/refman/5.0/en/join.html or show us what you have tried. — ggdx, Aug 15 '14 at 10:37
@bluefeet, That isn't quite the same question though is it? If you took the time to read each question before marking as a duplicate it would help. This one contains an actual SUM, as well as a pivot. — Arth, Aug 15 '14 at 10:41
@Arth I'm pretty sure that the user could get the result using the many examples in that duplicate. I'm also fully aware of what that duplicate question contained before I marked this as a dup. They would still use either `count` or `sum` as the aggregate function while pivoting. For example [this](http://stackoverflow.com/a/19009871/426671) answer shows a `sum()`, the difference would be they would pivot the months. — Taryn, Aug 15 '14 at 10:44
@bluefeet Yes, they could get most of the way towards their solution but that isn't quite the same. I quote from the dupe box: 'If those answers do not FULLY address your question, please ask a new question.' — Arth, Aug 15 '14 at 10:48

Praveen Prasannan · Answer 1 · 2014-08-15T11:19:43.887

Easier answer by @Arth

select project_id,
         CONCAT('title', project_id) title,
         SUM(month=1) month_1,
         SUM(month=2) month_2,
         SUM(month=3) month_3
    from table1 test
group by project_id;

Fiddle

select
    `PROJECT_ID`,Title,
    coalesce(count(`MONTH_1`),0) as `MONTH_1`,
    coalesce(count(`MONTH_2`),0) as `MONTH_2`,
    coalesce(count(`MONTH_3`),0) as `MONTH_3` 
  from
(select
    `PROJECT_ID`, concat('Title', `PROJECT_ID`) as Title,
    case when `MONTH` = 1 then `MEMBER_ID` end as `MONTH_1`,
    case when `MONTH` = 2 then `MEMBER_ID` end as `MONTH_2`,
    case when `MONTH` = 3 then `MEMBER_ID` end as `MONTH_3`
  from Table1) test
  group by PROJECT_ID;

fiddle

This is hugely overcomplicated: [SEE FIDDLE](http://sqlfiddle.com/#!2/947ad/9). Feel free to use this as I can't add an answer. — Arth, Aug 15 '14 at 10:58

Pivot table Mysql

1 Answers1