Pivot in a report with dynamic columns

I am in need to create a report with dynamic columns.
The columns are weeks number, starting from actual week (12-2022), going back for 52 weeks (13-2021) in a rolling year way.
The report will be executed once per week, so next week I'll have 13-2022 up to 14-2021 and so on.
Data comes from some columns:
External consultant in charge of a customer, name of the customer, name of the person of the customer who provided an order, weeks number, year number, email address, day of the order (for this field duplicates are admitted).
I am in need to have the number of the orders done each week, using a normal sql count.
I can eventually create a pivot with rolling week numbers, with some code like that:
select * into tab from cust_table
declare @sql nvarchar(max), @cols varchar(max)
select @cols = coalesce(@cols + ',', '') + ''
In this case I should create a variable and use those variables (52, like the number of the weeks in a year) and define them.
Is it there any more simple solution?
Thank you!

When you say report do you mean this kind of report?
www.etl-tools.com/wiki/aetle/reports?s[]=report

Yes Mike,
unfortunately I am in need to create a pdf report for each of my colleague and send it via email.

Looks like an interesting challenge

Can you prove us with some examples, please

Here how my source data looks like (excel file)
And here is what I am trying to create (excel file)

Perfect, I'll try to create a test environment. And I'll give you some excel files.

Hi Mike,
here it is: it's an excel file with a pivot inside.

db is the database, while sheet 1 is the graphical representation of the database that I would like to get.

Each "Representative" should receive it's own subset of data in a pdf, once per week, and the row containing week numbers should be desc (current week is the first, last week is current week+1 of the last year).

Thank you!