How can I pivot SQL query results by date and location for the last 3 report dates?

I have table (UserReport), that lists users that are missing company required AD attributes. In this table I have ReportDate column (which is the date of the weekly report I run). I have a different locations field called Site rolls up to Division. I want to give a historical pivot per site for that last 3 reportdates and the counts per site. Below is what I am getting now.

SQL Query Ran

SELECT division, site, reportdate, COUNT(reportdate) as CountUsers 
FROM UserReport
WHERE division = 'US South'
GROUP BY division, site, reportdate

RESULTS

RESULTS that I want

You're missing something into your pivot approach: splitting the date in the three dates you need. Once you split those values, you can apply conditional aggregation, and you'll get your output.

Using the SUM in place of the COUNT could avoid you issues, in case one column does not have any non-null value.

SELECT division, 
       site, 
       SUM(CASE WHEN reportdate = '2023-05-19' THEN 1 ELSE 0 END) AS date1, 
       SUM(CASE WHEN reportdate = '2023-05-24' THEN 1 ELSE 0 END) AS date2,
       SUM(CASE WHEN reportdate = '2023-05-26' THEN 1 ELSE 0 END) AS date3
FROM UserReport
WHERE division = 'US South'
GROUP BY division, site

This should be good, given that you indicated you want only those last three dates.

Although if in future you would require to generalize on dates, then you would need a dynamic query, which is heavier in performance and has some issues tied to it. Doing such query also would require knowledge ofthe DBMS you're currently using, as each DBMS employs different syntax for dynamic querying.