Transform table based on dates and distribute counts of files-CodePudding

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 [dbo].[FileProcessLog] a
LEFT JOIN [dbo].[MasterFile] 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)

Sample Output of the query:

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.

CodePudding user response：

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?