SQL Pivot And Group By By Date And Totals

Contents hide

1 SQL Pivot and Group By By Date and Totals

Problem Description:

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 – 1

You can find the answer here : https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/
and below

USE [tempdb] 
IF OBJECT_ID ('[Date]') IS NOT NULL DROP TABLE [Date] 
CREATE TABLE [Date] ([Id] int , [OperationType] varchar(50) , [Date] datetime  , [ResultCode] int) 
INSERT INTO [Date] 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 @pivotcolumns varchar(500) = STUFF((SELECT ',' + QUOTENAME(CAST([ResultCode] AS varchar(20))) FROM [Date] GROUP BY [ResultCode] ORDER BY [ResultCode] FOR XML PATH('')),1,1,'')

DECLARE @SqlStatement NVARCHAR(MAX)   
SET @SqlStatement = N'
    SELECT * , SUM('+REPLACE(@pivotcolumns, ',', '+')+') OVER (PARTITION BY CAST([Date] AS date)) AS "Total"     FROM (
      SELECT
        CAST([Date] AS date) AS "Date", [ResultCode]
      FROM [Date]
    ) AS t1
    PIVOT (
      COUNT([ResultCode])
      FOR [ResultCode]
      IN (
        '[email protected]+'
      )
    ) AS PivotTable
      ';
EXEC(@SqlStatement)

Solution – 2

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],'[email protected]+', 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 (
        '[email protected]+'
      )
    ) AS P'; 

EXEC(@QUERY)

DROP TABLE IF EXISTS #ResultCodes

Rate this post