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