SQL Server 2019

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

CREATE TABLE dbo.JetSales
(
  ID        int,
  Name      nvarchar(32),
  SalesDate date
);
INSERT dbo.JetSales(ID, Name, SalesDate) VALUES
(1,N'John','20211005'),(1,N'John','20211016'),(1,N'John','20211031'),
(2,N'Mary','20211007'),(2,N'Mary','20211013'),
(3,N'Tank','20211009');

DECLARE @thisMonth date, @firstMonth date;
SET @thisMonth = DATEFROMPARTS(YEAR(getdate()), MONTH(getdate()), 1);
SET @firstMonth = DATEADD(MONTH, -5, @thisMonth);

--Desired query:

;WITH src AS 
(
  SELECT ID, Name, m = CONVERT(char(10), 
      DATEFROMPARTS(YEAR(SalesDate), Month(SalesDate), 1), 101)
  FROM dbo.JetSales
  WHERE SalesDate >= @firstMonth
),
agg AS
(
  SELECT ID, Name, m, c = COUNT(*)
  FROM src GROUP BY ID, Name, m
)
SELECT ID, Name,
  [10/01/2021] = COALESCE([10/01/2021], 0),
  [11/01/2021] = COALESCE([11/01/2021], 0),
  [12/01/2021] = COALESCE([12/01/2021], 0),
  [01/01/2022] = COALESCE([01/01/2022], 0),
  [02/01/2022] = COALESCE([02/01/2022], 0),
  [03/01/2022] = COALESCE([03/01/2022], 0)
FROM agg PIVOT (SUM(c) FOR m IN (
  [10/01/2021],[11/01/2021],[12/01/2021],
  [01/01/2022],[02/01/2022],[03/01/2022]
)) AS p;

-- how to get there:

DECLARE @col1 nvarchar(max) = N'',
        @col2 nvarchar(max) = N'',
        @sql  nvarchar(max) = N';WITH src AS 
(
  SELECT ID, Name, m = CONVERT(char(10), 
      DATEFROMPARTS(YEAR(SalesDate), Month(SalesDate), 1), 101)
  FROM dbo.JetSales
  WHERE SalesDate >= @firstMonth
),
agg AS
(
  SELECT ID, Name, m, c = COUNT(*)
  FROM src GROUP BY ID, Name, m
)
SELECT ID, Name,';

;WITH m(m) AS 
(
  SELECT @firstMonth
  UNION ALL 
  SELECT DATEADD(MONTH, 1, m) FROM m
  WHERE m < @thisMonth
),
x(x) AS 
(
  SELECT QUOTENAME(CONVERT(char(10), m, 101)) FROM m
)
SELECT 
  @col1 += STRING_AGG(CONCAT(N'
', x, N' = COALESCE(', x, ',0)'),N','),
  @col2 += STRING_AGG(x, N',
')
FROM x;

SET @sql += @col1 + N'
FROM agg PIVOT (SUM(c) FOR m IN (' 
+ @col2 + N'
)) AS p;';

SELECT @sql;
EXEC sys.sp_executesql @sql, N'@firstMonth date', @firstMonth;

ID	Name	10/01/2021	11/01/2021	12/01/2021	01/01/2022	02/01/2022	03/01/2022

(No column name)
;WITH src AS ( SELECT ID, Name, m = CONVERT(char(10), DATEFROMPARTS(YEAR(SalesDate), Month(SalesDate), 1), 101) FROM dbo.JetSales WHERE SalesDate >= @firstMonth ), agg AS ( SELECT ID, Name, m, c = COUNT(*) FROM src GROUP BY ID, Name, m ) SELECT ID, Name, [03/01/2024] = COALESCE([03/01/2024],0), [04/01/2024] = COALESCE([04/01/2024],0), [05/01/2024] = COALESCE([05/01/2024],0), [06/01/2024] = COALESCE([06/01/2024],0), [07/01/2024] = COALESCE([07/01/2024],0), [08/01/2024] = COALESCE([08/01/2024],0) FROM agg PIVOT (SUM(c) FOR m IN ([03/01/2024], [04/01/2024], [05/01/2024], [06/01/2024], [07/01/2024], [08/01/2024] )) AS p;

(No column name)

;WITH src AS
(
SELECT ID, Name, m = CONVERT(char(10),
DATEFROMPARTS(YEAR(SalesDate), Month(SalesDate), 1), 101)
FROM dbo.JetSales
WHERE SalesDate >= @firstMonth
),
agg AS
(
SELECT ID, Name, m, c = COUNT(*)
FROM src GROUP BY ID, Name, m
)
SELECT ID, Name,
[03/01/2024] = COALESCE([03/01/2024],0),
[04/01/2024] = COALESCE([04/01/2024],0),
[05/01/2024] = COALESCE([05/01/2024],0),
[06/01/2024] = COALESCE([06/01/2024],0),
[07/01/2024] = COALESCE([07/01/2024],0),
[08/01/2024] = COALESCE([08/01/2024],0)
FROM agg PIVOT (SUM(c) FOR m IN ([03/01/2024],
[04/01/2024],
[05/01/2024],
[06/01/2024],
[07/01/2024],
[08/01/2024]
)) AS p;

ID	Name	03/01/2024	04/01/2024	05/01/2024	06/01/2024	07/01/2024	08/01/2024