SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE appointment (date TEXT, status TEXT);
✓
INSERT INTO appointment VALUES
  ('2022-01-01', 'PENDING'), ('2022-01-01', 'PENDING'), ('2022-01-01', 'PENDING'), 
  ('2022-03-01', 'PENDING'), 
  ('2022-04-01', 'PENDING'), ('2022-04-01', 'PENDING'), 
  ('2022-05-01', 'PENDING'), 
  ('2022-10-01', 'PENDING'), 
  ('2022-12-01', 'PENDING');
✓
WITH months(month) AS (VALUES ('01'), ('02'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'), ('09'), ('10'), ('11'), ('12'))
SELECT DISTINCT GROUP_CONCAT(COUNT(a.date)) OVER (
                  ORDER BY m.month 
                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                ) result 
FROM months m LEFT JOIN appointment a
ON strftime('%m', a.date) = m.month AND strftime('%Y', a.date) = '2022' AND a.status LIKE '%PENDING%' 
GROUP BY m.month;


result


3,0,1,2,1,0,0,0,0,1,0,1