sqlmysqlpivot

pivot a query adding information from another table


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

Solution

  • 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