pivot a query adding information from another table

Question

I need help trying to pivot the table derived from the following query:

WITH RECURSIVE dates AS 
(
    SELECT start_date as period, end_date
    FROM progetti
    WHERE id = 1
    UNION ALL
    SELECT DATE_ADD(period, INTERVAL 1 MONTH), end_date
    FROM dates 
    WHERE period < end_date - INTERVAL 1 MONTH
)
SELECT DATE_FORMAT(d.period, '%m-%Y') AS period, COALESCE(total, 0) AS total, COALESCE(s.tipo,0) AS tipo
FROM dates d
LEFT JOIN (
  SELECT DATE_FORMAT(created_on, '%m-%Y') as 'period', 
  COUNT(items_header.id) as 'total' ,
  items_header.tipo 
  FROM items_header  
  WHERE progetto=1
  GROUP BY DATE_FORMAT(created_on, '%m-%Y')
) s on s.period = DATE_FORMAT(d.period, '%m-%Y');

and returns the following set of data:

period	total	tipo
01-2022	0	0
02-2022	0	0
03-2022	1	1
03-2022	1	3
04-2022	1	3
05-2022	3	1
06-2022	2	1
07-2022	0	0
08-2022	0	0
09-2022	0	0
10-2022	0	0
11-2022	0	0
12-2022	0	0

What I am trying to build is a different result:

period	tipo 1	tipo=2	tipo=3	tipo=4
01-2022	0	0	0	0
02-2022	0	0	0	0
03-2022	1	0	1	0
04-2022	0	0	1	0
05-2022	3	1	0	0
06-2022	2	0	0	0
07-2022	0	0	0	0
08-2022	0	0	0	0
09-2022	0	0	0	0
10-2022	0	0	0	0
11-2022	0	0	0	0
12-2022	0	0	0	0

So that months are repeated only once and the count of items is splitted by tipo in columns. I am trying to build a dataset that will populate a chart.js graph (one line for each tipo) and I am looking how to build it in mysql.

EDIT: items_header table

ID	created_on	tipo
1	2022-03-03	1

and so on (it is a ticketing system header table)

item_type_catalog table

id	description
1	A
2	B
3	C
4	D

Answer 1

Basically it is a simple pivot, as there are only 5 types and you can easily add more by adding a new MAX(CASE WHEN line to the last SELECT

WITH RECURSIVE dates AS 
(
    SELECT start_date as period, end_date
    FROM progetti
    WHERE id = 1
    UNION ALL
    SELECT DATE_ADD(period, INTERVAL 1 MONTH), end_date
    FROM dates 
    WHERE period < end_date - INTERVAL 1 MONTH
), CTE aS (
SELECT DATE_FORMAT(d.period, '%m-%Y') AS period, COALESCE(total, 0) AS total, COALESCE(s.tipo,0) AS tipo
FROM dates d
LEFT JOIN (
  SELECT DATE_FORMAT(created_on, '%m-%Y') as 'period', 
  COUNT(items_header.id) as 'total' ,
  items_header.tipo 
  FROM items_header  
  WHERE progetto=1
  GROUP BY DATE_FORMAT(created_on, '%m-%Y'), items_header.tipo
) s on s.period = DATE_FORMAT(d.period, '%m-%Y'))
  SELECT 
  period
    ,MAX(CASE WHEN tipo = 1 THEN total ELSE 0 END) tipo_1
  ,MAX(CASE WHEN tipo = 2 THEN total ELSE 0 END) tipo_2
  ,MAX(CASE WHEN tipo = 3 THEN total ELSE 0 END) tipo_3
  ,MAX(CASE WHEN tipo = 4 THEN total ELSE 0 END) tipo_4
FROM CTE 
GROUP BY period
ORDER BY period

period	tipo_1	tipo_2	tipo_3	tipo_4
01-2022	0	0	0	0
02-2022	0	0	0	0
03-2022	1	0	1	0
04-2022	0	0	1	0
05-2022	3	0	0	0
06-2022	2	0	0	0
07-2022	0	0	0	0
08-2022	0	0	0	0
09-2022	0	0	0	0
10-2022	0	0	0	0
11-2022	0	0	0	0
12-2022	0	0	0	0

fiddle