SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TableA 
(
    CustomerID	INTEGER,
    InternalID	INTEGER,
    AsOfDate	DATE
);

INSERT INTO TableA (CustomerID,InternalID,AsOfDate)
VALUES
    ('610', '016', '11/30/2021'),
    ('610', '016', '11/29/2021'),
    ('610', '016', '11/28/2021'),
    ('610', '016', '10/29/2021'),
    ('610', '016', '10/15/2021'),
    ('610', '016', '9/15/2021'),
    ('610', '016', '9/14/2021'),
    ('610', '016', '8/10/2021'),
    ('725', '527', '11/30/2021'),
    ('725', '527', '11/29/2021'),
    ('725', '527', '11/28/2021'),
    ('725', '527', '10/29/2021'),
    ('725', '527', '10/15/2021'),
    ('725', '527', '9/15/2021'),
    ('725', '527', '9/14/2021'),
    ('725', '527', '8/10/2021');
    
CREATE TABLE TableB 
(
    Spend	DECIMAL(12,2),
    InternalID	INTEGER,
    StoreNum	INTEGER,
    AsOfDate	DATE
);

INSERT INTO TableB (Spend,InternalID,StoreNum,AsOfDate)
VALUES
    ('5000', '016', '123', '11/30/2021'),
    ('4000', '016', '321', '11/30/2021'),
    ('3000', '016', '456', '11/28/2021'),
    ('4500', '016', '654', '10/29/2021'),
    ('5000', '016', '789', '10/15/2021'),
    ('4000', '016', '756', '9/15/2021'),
    ('9000', '016', '987', '9/15/2021'),
    ('1000', '016', '545', '8/10/2021'),
    ('6000', '527', '124', '11/30/2021'),
    ('4500', '527', '354', '11/30/2021'),
    ('9000', '527', '745', '11/28/2021'),
    ('8500', '527', '846', '10/29/2021'),
    ('7500', '527', '646', '10/29/2021'),
    ('6000', '527', '311', '9/14/2021'),
    ('3000', '527', '211', '9/14/2021'),
    ('2500', '527', '121', '8/10/2021');
32 rows affected

SELECT ROW_NUMBER() OVER(ORDER BY MAX(AsOfDate) DESC) AS RowNum, MAX(AsOfDate) EndOfMonth
FROM TableA
WHERE AsOfDate > DATEADD(year, -1, GETDATE())
GROUP BY YEAR(AsOfDate), MONTH(AsOfDate);
    
SELECT DISTINCT CustomerID
FROM TableA
WHERE AsOfDate > DATEADD(year, -1, GETDATE());


RowNum
EndOfMonth




CustomerID


WITH customers (CustomerID) AS (
    SELECT DISTINCT CustomerID
    FROM TableA
    WHERE AsOfDate > DATEADD(year, -1, GETDATE())
), months (RowNum, EndOfMonth) AS (
    SELECT ROW_NUMBER() OVER(ORDER BY MAX(AsOfDate) DESC) AS RowNum, MAX(AsOfDate) EndOfMonth
    FROM TableA
    WHERE AsOfDate > DATEADD(year, -1, GETDATE())
    GROUP BY YEAR(AsOfDate), MONTH(AsOfDate)
)
SELECT
    customers.CustomerID,
    SUM(CASE WHEN months.RowNum =  1 THEN TableB.Spend END) AS TotalSpend1,
    SUM(CASE WHEN months.RowNum =  2 THEN TableB.Spend END) AS TotalSpend2,
    SUM(CASE WHEN months.RowNum =  3 THEN TableB.Spend END) AS TotalSpend3,
    SUM(CASE WHEN months.RowNum =  4 THEN TableB.Spend END) AS TotalSpend4,
    SUM(CASE WHEN months.RowNum =  5 THEN TableB.Spend END) AS TotalSpend5,
    SUM(CASE WHEN months.RowNum =  6 THEN TableB.Spend END) AS TotalSpend6,
    SUM(CASE WHEN months.RowNum =  7 THEN TableB.Spend END) AS TotalSpend7,
    SUM(CASE WHEN months.RowNum =  8 THEN TableB.Spend END) AS TotalSpend8,
    SUM(CASE WHEN months.RowNum =  9 THEN TableB.Spend END) AS TotalSpend9,
    SUM(CASE WHEN months.RowNum = 10 THEN TableB.Spend END) AS TotalSpend10,
    SUM(CASE WHEN months.RowNum = 11 THEN TableB.Spend END) AS TotalSpend11,
    SUM(CASE WHEN months.RowNum = 12 THEN TableB.Spend END) AS TotalSpend12
FROM customers
CROSS JOIN months
LEFT JOIN TableA
    ON customers.CustomerID = TableA.CustomerID
    AND months.EndOfMonth = TableA.AsOfDate
LEFT JOIN TableB
    ON TableA.InternalID = TableB.InternalID
    AND TableA.AsOfDate = TableB.AsOfDate
GROUP BY customers.CustomerID
ORDER BY customers.CustomerID ASC;


CustomerID
TotalSpend1
TotalSpend2
TotalSpend3
TotalSpend4
TotalSpend5
TotalSpend6
TotalSpend7
TotalSpend8
TotalSpend9
TotalSpend10
TotalSpend11
TotalSpend12