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

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

CodePudding user response：

execute immediate (             
  select '''
    select * 
    from (select item, qty, date from table)
    pivot(sum(qty) for date in ("''' ||  string_agg(distinct CAST(date AS STRING), '", "' order by CAST(date AS STRING))  || '''"))
  '''
  from table

);

CodePudding user response：

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