SQL Pivot and Group By By Date and Totals

I am using SQL Server 13.0 Developer Edition. I can't make the correct structure for the SQL code with Pivot and Group by clauses.

I have data like;

Id	OperationType	Date	ResultCode
1	BeginTransaction	2022-12-01 16:54:30	-28
2	BeginTransaction	2022-12-02 18:54:30	-30
3	BeginTransaction	2022-12-02 18:54:30	-30
4	BeginTransaction	2022-12-03 14:54:30	-10
5	BeginTransaction	2022-12-03 11:54:30	-5
6	BeginTransaction	2022-12-05 10:54:30	-3

and I want to see total number of ResultCodes per day but I want to generate ResultCode columns dynamicly because I have so much different result codes. Query result should be like;

Day	-3	-5	-10	-28	-30	Total
2022-12-01	0	0	0	1	0	1
2022-12-02	0	0	0	0	2	2
2022-12-03	0	1	1	0	0	2
2022-12-05	1	0	0	0	0	1

I wrote this query but it says The incorrect value "ResultCode" is supplied in the PIVOT operator.

Select * from (SELECT CAST(Date as date),
COUNT(ResultCode) as Result,
       COUNT(*) AS Totals
FROM OperationLogs 
WHERE OperationType = 'Begin'
GROUP BY CAST(StartTime as date)
) As Source
PIVOT (
COUNT(Result) FOR Result IN ([ResultCode])
) AS PivotTable
ORDER BY ForDate

Can anyone help me with how can I group by date and also have counts for ResultCodes as colums and a Total by day?

Solution

CREATE TABLE #ResultCodes (
Id INT,
OperationType VARCHAR(50),
[Date] DateTime,
ResultCode INT
)

INSERT INTO #ResultCodes(Id,OperationType,[Date],ResultCode) VALUES
(1,'BeginTransaction','2022-12-01 16:54:30',-28),
(2,'BeginTransaction','2022-12-02 18:54:30',-30),
(3,'BeginTransaction','2022-12-02 18:54:30',-30),
(4,'BeginTransaction','2022-12-03 14:54:30',-10),
(5,'BeginTransaction','2022-12-03 11:54:30',-5),
(6,'BeginTransaction','2022-12-05 10:54:30',-3)

DECLARE @COLUMNS AS NVARCHAR(MAX)
DECLARE @QUERY  AS NVARCHAR(MAX)
    
SET @COLUMNS = STUFF((SELECT ',' + QUOTENAME(ResultCode) 
             FROM #ResultCodes GROUP BY ResultCode ORDER BY ResultCode DESC
             FOR XML PATH(''), TYPE
             ).value('.', 'NVARCHAR(MAX)') 
         ,1,1,'')

SET @QUERY = N'
    SELECT [Date],'+@COLUMNS+', tc AS "Total"    
    FROM (
      SELECT
        [tc] = COUNT(CAST([Date] AS date)) over(partition by CAST([Date] AS date)),
        CAST([Date] AS date) AS "Date", 
        [ResultCode]
      FROM #ResultCodes
    ) AS tb 
    PIVOT (
      COUNT([ResultCode])
      FOR [ResultCode]
      IN (
        '+@COLUMNS+'
      )
    ) AS P'; 

EXEC(@QUERY)

DROP TABLE IF EXISTS #ResultCodes