Transform table based on dates and distribute counts of files

Question

Query Q1 gives me table in which there is total count of file received for particular date in a country.

Q1:

select
  b.country,
  CAST(a.ProcessDate AS Date) AS DATE,
  count(a.ProcessDate) AS total
from Log a
LEFT JOIN Files b ON a.FileID = b.FileID
where a.ProcessDate BETWEEN '2022-10-01' AND '2022-10-30'
GROUP BY
  b.Country,
  CAST(a.ProcessDate AS DATE)

Now I want this table to transform into below table based on date column as header and also count of files should be distributed based on country like below table:

I need SQL for transforming Q1 to above table format. I was trying to use Pivot but not able to write correct sql which will give desire output.

Answer 1

Pivot's simple syntax makes for hard to write columns.

Here is a short example.

with t (Country ,Date,total)
as
(
Select  'newzealand'                 ,    '2022-10-03',21
Union ALL Select 'argentina'         ,    '2022-10-04',5
Union ALL Select 'brazil'            ,    '2022-10-04',4
Union ALL Select 'chile'             ,    '2022-10-05',22
Union ALL Select 'mexico'            ,    '2022-10-05',34
Union ALL Select 'peru'              ,    '2022-10-06',1

)

Select 
    *
from
    (
Select 
    Date,
    Total,
    Country
from t
    ) x
Pivot(
    sum(total)
    for Date in (
            [2022-10-03],
            [2022-10-04],
            [2022-10-05],
            [2022-10-06]
    )
) as pivottable

You can then make this example dynamic, it is best to read the post for this, it is explained very well in it.

SQL Server dynamic PIVOT query?