SQL - Counting and Displaying as Columns the Week data

The website I am working on has a SQL report that has been requested to be used as a basis for a new report. The old report takes the usage records of items within the date range and uses it to calculate the total weight with the items being split into their respective item groups so the group's respective total weight usage can be shown as well.

I have cut down the code to its basic form to detail how it currently works

with td as(
select
    g.description as "ItemGroup"
    ,u.itemcode as "ItemCode"
    ,i.description as "ItemDescription"
    ,sum(cast(i.weight as real) * usage) as "Weight"
from
    usage u
inner join 
    items i
    on i.code = u.itemcode
inner join
    groups g
    on g.code = i.groupcode
where
    date(updatedate) > date(@FROM) and date(updatedate) <= date(@TO)

group by g.description,u.itemcode,i.description)

select 'Detail' as "type",td.* from td
union
select 'Group Total' as "type",itemgroup,null,sum(weight) from td group by itemgroup
union
select 'Total' as "type",'' as itemgroup,null,sum(weight) from td
order by type, itemcode

However the new report they request expands the report to show the individual Week usage totals as columns as well (Week 1,2,...43). Now the problem I see with this is that the SQL would have to know how many weeks are within the Date Range to create columns and then use a where clause equivalent to find the values within that date range for each week. And I don't know if that is even possible with SQL.

I had a look on google and could not find anything related on the matter. I would like to be proven wrong as that would be a powerful tool to have in SQL. Can someone tell me if this is possible?