create as many new columns as there are months and fill them by sum of quantity

I have these data :

Date	Item	Qty
2022-01-01	A	10
2022-01-01	A	30
2022-01-01	B	40
2022-02-01	A	20
2022-02-01	C	10

I would like to create as many new columns as there are months and sum the qty group by Month and by item like this :

Item	JAN	FEB
A	40	20
B	40	0
C	0	10

Thanks a lot for your help

Answer

You indeed need scripting for dynamic range of Month and year of data.

With the sample you have provided, you can achive by restricting two month ‘January2022’ and ‘Febuary2022’ as:

with sample_data as (
  select '2022-01-01' as Date ,'A' as Item,10 as Qty union all
select '2022-01-01','A',30 union all
select '2022-01-01','B',40 union all
select '2022-02-01','A',20 union all
select  '2022-02-01','C',10)

select * from(
select  format_date('%B%Y', date(Date)) Month,Item,Qty from sample_data ) pivot(sum(Qty)  for Month in ('January2022','February2022')
)


[enter image description here][1]


  [1]: https://i.stack.imgur.com/FsYLd.jpg