SQL Server dynamic pivot table with dates as columns using CTE as my table?

Question

So I have 36 month worth of data in my CTE, and the database is still active storing new data daily.

In my analysis, I just need 6 months worth of data from getdate()

So my question is that how do I make the month derived from calendar month the column of my table?

So for end of March 2022, this is the view that I should see:

id	name	10/01/21	11/01/21	12/01/21	01/01/22	02/01/22	03/01/2022
1	John	3	0	1	0	0	2
2	Mary	6	1	2	1	1	2
3	Angelo	1	5	3	2	2	0
4	Diane	3	2	0	1	0	6

So for the end of April 2022, this is the view that I should see:

id	name	11/01/21	12/01/21	01/01/22	02/01/22	03/01/2022	04/01/22
1	John	0	1	0	0	2	7
2	Mary	1	2	1	1	2	2
3	Angelo	5	0	0	0	0	3
4	Diane	2	0	1	0	6	4

Answer 1

You can't have a dynamic PIVOT without dynamic SQL. I find it's easiest to break it up into parts.

1. Get the last 6 months

    DECLARE @thisMonth date, @firstMonth date;
    SET @thisMonth = DATEFROMPARTS(YEAR(getdate()), MONTH(getdate()), 1);
    SET @firstMonth = DATEADD(MONTH, -5, @thisMonth);
   
    ;WITH m(m) AS 
    (
      SELECT @firstMonth
      UNION ALL 
      SELECT DATEADD(MONTH, 1, m) FROM m
      WHERE m < @thisMonth
    )
    SELECT m FROM m ORDER BY m DESC;
   

   Output:

   m
   2022-03-01
   2022-02-01
   2022-01-01
   2021-12-01
   2021-11-01
   2021-10-01

2. Figure out what manual query you need. Given this sample data:

    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');
   

   I think you want a query like this (yes, you can accomplish this specific task with PIVOT too, but PIVOT doesn't cover some other scenarios, and it also requires pre-aggregation in this case... so I think conditional aggregation is better):

    SELECT ID, Name,  
      [10/01/2021] = SUM(CASE WHEN SalesDate >= '20211001' 
        AND SalesDate < '20211101' THEN 1 ELSE 0 END), 
      [11/01/2021] = SUM(CASE WHEN SalesDate >= '20211101' 
        AND SalesDate < '20211201' THEN 1 ELSE 0 END), 
      [12/01/2021] = SUM(CASE WHEN SalesDate >= '20211201' 
        AND SalesDate < '20220101' THEN 1 ELSE 0 END), 
      [01/01/2022] = SUM(CASE WHEN SalesDate >= '20220101' 
        AND SalesDate < '20220201' THEN 1 ELSE 0 END), 
      [02/01/2022] = SUM(CASE WHEN SalesDate >= '20220201' 
        AND SalesDate < '20220301' THEN 1 ELSE 0 END), 
      [03/01/2022] = SUM(CASE WHEN SalesDate >= '20220301' 
        AND SalesDate < '20220401' THEN 1 ELSE 0 END)
    FROM dbo.JetSales AS js GROUP BY ID, Name;
   

3. Which you can build as follows:

    DECLARE @thisMonth date, @firstMonth date;
    SET @thisMonth = DATEFROMPARTS(YEAR(getdate()), MONTH(getdate()), 1);
    SET @firstMonth = DATEADD(MONTH, -5, @thisMonth);
   
    DECLARE @sql nvarchar(max) = N'SELECT ID, Name';
   
    ;WITH m(m) AS 
    (
      SELECT @firstMonth
      UNION ALL 
      SELECT DATEADD(MONTH, 1, m) FROM m
      WHERE m < @thisMonth
    )
    SELECT @sql += N', 
    ' + QUOTENAME(CONVERT(char(10), m, 101)) 
      + N' = SUM(CASE WHEN SalesDate >= ' 
      + QUOTENAME(CONVERT(char(8), m, 112), char(39)) + N' 
          AND SalesDate < '
      + QUOTENAME(CONVERT(char(8), DATEADD(MONTH, 1, m), 112), char(39))
      + N' THEN 1 ELSE 0 END)'
    FROM m;
   
    SET @sql += N'
    FROM dbo.JetSales AS js GROUP BY ID, Name;';
   
    SELECT @sql;
    EXEC sys.sp_executesql @sql;
   

Working example: db<>fiddle

If you really want to use PIVOT explicitly, you can, it's just a lot more cumbersome. Here's the query you want to end up with:

;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;

To get there:

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

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;

Another fiddle here: db<>fiddle