Considering the sample table below:

What would be the best way to get a column for each month from the last 12 months, with the sum of amount for each product? I'd like to get something like this:

I'm trying to use the PIVOT function, but I haven't been able to make it work:

select * ,
  monthname(to_date(Date)) month  
  from PRODUCTS
    pivot(sum(Amount) for month in ('Jan','Feb','Mar','Apr','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))
      as p
  WHERE Date >= '2021-08-01'
  order by Product;

CodePudding user response：

Try this, using date format TO_VARCHAR(Date, 'MON') AS Month:

WITH PRODUCTS AS (
  
  SELECT Product, Amount, TO_DATE(DateText, 'MON-DD-YYYY') AS Date
      FROM (VALUES 
  ('p1', 5 , 'jan-1-2022'),
  ('p1', 7 , 'jan-7-2022'),
  ('p1', 7 , 'feb-17-2022'),
  ('p2', 12, 'jan-18-2022'),
  ('p2', 16, 'feb-1-2022'),
  ('p2', 16, 'feb-4-2022'),
  ('p3', 23, 'jan-28-2022'),
  ('p4', 2 , 'mar-22-2022'),
  ('p4', 1 , 'mar-4-2022'))t(Product, Amount, DateText)
  
), ADD_MONTH AS (
  
    SELECT Product, Amount, TO_VARCHAR(Date, 'MON') AS Month
      FROM PRODUCTS
     WHERE Date >= '2021-08-01'
)
SELECT Product, Jan, Feb, Mar, Apr, Jun, Jul, Aug, Sep, Oct, Nov, Dec
  FROM ADD_MONTH
 PIVOT (SUM(Amount) FOR Month IN ('Jan','Feb','Mar','Apr','Jun','Jul','Aug','Sep','Oct','Nov','Dec')) AS p(Product, Jan, Feb, Mar, Apr, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
 ORDER BY Product;

CodePudding user response：

you could try as follows.

 SELECT * FROM (  
select column1 as product ,  column2 as amount, monthname(to_date(COLUMN3, 'MON-DD-YYYY')) as date1 from values
('p1',  5,   'jan-1-2022' ),
('p1',  7,  'jan-7-2022'),
('p1',  7,   'feb-17-2022'),
('p2',  12,  'jan-18-2022'),
('p2',  16,  'feb-1-2022'),
('p2',  16,  'feb-4-2022'),
('p3',  23,  'jan-28-2022'),
('p4',  2,   'mar-22-2022'),
('p4',  1,   'mar-4-2022')
  )
   --pivot(max(service_date) for dose in ('dose1', 'dose2'))
   pivot(sum(amount) for date1 in  ('Jan','Feb','Mar','Apr','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))
   as p (product,  Jan,Feb,Mar,Apr,Jun,Jul,Aug,Sep,Oct,Nov,Dec);