I've 2 tables methods and activities

// methods
 --- ------------- 
| id | name       |
 ---- ------------ 
| 1  | Goblins    |
| 2  | Valkyrie   |

// activities
 ----------- ---- --------------------- ------------ ------------ 
| method_id | id | name                | start      | end        |
 ----------- --------------------- ------------ ----------------- 
| 1         | 1  | Attack Village A    | 2019-01-01 | 2019-01-02 |
| 1         | 2  | Attack Village B    | 2019-01-03 | 2019-01-04 |
| 2         | 3  | Attach Village A    | 2019-02-01 | 2019-02-02 |

How can I get a matrix table group by method and month. expected result:

CodePudding user response：

to group by the method first, the method table should have ID as a primary key; then you join the two tables in this way:

     Select Method.name as name, Activities.name as Activity, MONTHNAME(Activities.start) as Activity_Method
     From methods as Method join activities as Activities
     Where Method.ID = Activities.method_id

you will get this table: // Activity_Method

----------- --------------------- ------------

| name | Activity | start |

----------- --------------------- ------------

| Goblins | Attack Village A | January |

| Goblins | Attack Village B | January |

| Valkyrie | Attach Village A | February |

CodePudding user response：

Here I am ignoring the fact that the start and end period can extend between 2 or more separate months and taking start as the base for picking the month:

SELECT 
  m.`name` AS Method,
  (CASE WHEN MONTH(a.start)=1 THEN GROUP_CONCAT(a.`name`) ELSE '' END) AS January,
(CASE WHEN MONTH(a.start)=2 THEN GROUP_CONCAT(a.`name`) ELSE '' END) AS Febuary
FROM
`activities` a 
  JOIN methods m 
    ON a.`method_id` = m.`id` 
    GROUP BY a.`method_id`

This should produce you your result: