Transpose Row Counts over Columns on SQL Query

Question

I'm wondering if someone more adept at the SQL Pivot function could help me out. This is pretty basic, but for some reason I'm drawing a blank here.

I have the following script that gets error codes on a particular date:

SELECT GETDATE() as "Date"
,ErrorCode
FROM Application

This returns results like this.

Date	ErrorCode
3/16/2023	Code1
3/16/2023	Code1
3/16/2023	Code2
3/16/2023	Code2
3/16/2023	Code3
3/16/2023	Code3
3/16/2023	Code4
3/16/2023	Code4

However, I just need the total count of each error code on that date, so I need it to return this:

Date	Code1	Code2	Code3	Code4
3/16/2023	2	2	2	2

Anyone have any clue on how to use PIVOT for this? Or is there another solution I'm not thinking of?

Really appreciate any insight.

Answer 1

You didn't say what RDBMS you're using. This is in SQL Server.

Dynamic pivot Fiddle

Step one is to create a variable with your 'column names' in it. Once pivoted, your column names will be the values in your ErrorCode column.

DECLARE @columns NVARCHAR(MAX) = '';

  WITH cte AS (SELECT DISTINCT ErrorCode FROM temp)
SELECT @columns += QUOTENAME(ErrorCode) +',' 
  FROM cte 
 ORDER BY ErrorCode
SET @columns = LEFT(@columns, LEN(@columns)-1);

Using your sample data, @columns will equal

[Code1],[Code2],[Code3],[Code4]

Next we build the pivot inside of another variable. Since we only have the two columns, I used _date twice. The first _Date is to group the dates in the pivot, DateCount is the value we count, and errorCode is the category we pivot on.

(side note: don't use Date as a column name. Date is a reserved word and calling it in a query can cause issues.)

DECLARE @sql     NVARCHAR(MAX) = '';
  
SET @sql = '
SELECT * FROM 
( SELECT _Date 
       , _Date DateCount
       , ErrorCode
    FROM temp
) src
PIVOT
( COUNT(DateCount) FOR ErrorCode
    IN ('+ @columns +')
)pvt;';

EXECUTE sp_executesql @sql;

Result:

_Date	Code1	Code2	Code3	Code4
2023-03-16	2	2	2	2