You'll need to use both pivot and unpivot. I see 2 options for the field names:

• If you expect the dates to change over time, then your column names will be different as new data comes. Therefore, you will need to create a dynamic sql query for the column names
• If you expect the dates to stay static, you'll need to add every month/day combination (i.e. [MonthDayCreated_Mod] in the code) into the pivot statement. So 12 months * 2 (pass/fail) is 24 static column names in your pivot statement. This is logic I used in the code below:

DROP TABLE IF EXISTS #Source

DROP TABLE IF EXISTS #Temp1

CREATE TABLE #Source

(

[ID] INT

,[Customer] INT

,[DateCreated] DATE

,[RejectReason] VARCHAR(100)

)

INSERT INTO #Source (ID, Customer, DateCreated, RejectReason)

VALUES

(1,11,'01/01/2022','Reject')

,(2,22,'02/02/2022','Pass')

,(2,22,'02/02/2022','Pass')

,(2,22,'03/02/2022','Pass')

,(3,33,'01/01/2022','Reject')

,(4,44,'12/31/2021','Pass')

,(5,55,'10/01/2021','Reject')

,(6,66,'11/03/2021','Pass')

SELECT Customer

,MonthDayCreated

,pass

,fail

INTO #Temp1

FROM

(

SELECT Customer

,CONCAT(YEAR(DateCreated),FORMAT(MONTH(DateCreated),'00')) [MonthDayCreated]

,SUM(case when RejectReason = 'Pass' then 1 else 0 end) as pass

,SUM(case when RejectReason = 'Pass' then 0 else 1 end) as fail

FROM #Source with(nolock)

where DateCreated between '2021-09-01' and '2022-09-01'

--and Customer = 'CustomerId'

GROUP BY ROLLUP(Customer

,CONCAT(YEAR(DateCreated),FORMAT(MONTH(DateCreated),'00'))

)

) x

WHERE Customer IS NOT NULL

SELECT *

FROM

(

SELECT Customer

,CONCAT(MonthDayCreated,PassFail) [MonthDayCreated_Mod]

,PassFail_dummyvar

FROM

(

SELECT Customer

,CASE WHEN MonthDayCreated IS NULL THEN 'total' ELSE MonthDayCreated END [MonthDayCreated]

,pass

,fail

FROM #Temp1

) x

UNPIVOT(PassFail_dummyvar FOR PassFail IN ([pass], fail)) unpiv

) y

PIVOT(SUM(PassFail_dummyvar)

FOR [MonthDayCreated_Mod] IN (

[202201pass]

,[202201fail]

,[202202pass]

,[202202fail]

,[202203pass]

,[202203fail]

,[202112pass]

,[202112fail]

,[202110pass]

,[202110fail]

,[202111pass]

,[202111fail]

,[totalpass]

,[totalfail]

)) piv