How to pivot multiple aggregation in Snowflake

Question

I have the table structure as below

product_id	Period	Sales	Profit
x1	L13	$100	$10
x1	L26	$200	$20
x1	L52	$300	$30
x2	L13	$500	$110
x2	L26	$600	$120
x2	L52	$700	$130

I want to pivot the period column over and have the sales value and profit in those columns. I need a table like below.

product_id	SALES_L13	SALES_L26	SALES_L52	PROFIT_L13	PROFIT_L26	PROFIT_L52
x1	$100	$200	$300	$10	$20	$30
x2	$500	$600	$700	$110	$120	$130

I am using the snowflake to write the queries. I tried using the pivot function of snowflake but there I can only specify one aggregation function.

Can anyone help as how I can achieve this solution ?

Any help is appreciated.

Thanks

Answer 1

How about we stack sales and profit before we pivot? I'll leave it up to you to fix the column names that I messed up.

with cte (product_id, period, amount) as
  
(select product_id, period||'_profit', profit from t
 union all
 select product_id, period||'_sales', sales from t)
   
select * 
from cte
     pivot(max(amount) for period in ('L13_sales','L26_sales','L52_sales','L13_profit','L26_profit','L52_profit'))
     as p (product_id,L13_sales,L26_sales,L52_sales,L13_profit,L26_profit,L52_profit);

If you wish to pivot period twice for sales and profit, you'll need to duplicate the column so you have one for each instance of pivot. Obviously, this will create nulls due to duplicate column still being present after the first pivot. To handle that, we can use max in the final select. Here's what the implementation looks like

select product_id, 
       max(L13_sales) as L13_sales, 
       max(L26_sales) as L26_sales, 
       max(L52_sales) as L52_sales, 
       max(L13_profit) as L13_profit, 
       max(L26_profit) as L26_profit, 
       max(L52_profit) as L52_profit
from (select *, period as period2 from t) t
      pivot(max(sales) for period in ('L13','L26','L52'))
      pivot(max(profit) for period2 in ('L13','L26','L52'))  
      as p (product_id, L13_sales,L26_sales,L52_sales,L13_profit,L26_profit,L52_profit)
group by product_id;

At this point, it's an eyesore. You might as well use conditional aggregation or better yet, handle pivoting inside the reporting application. A more compact alternative of conditional aggregation uses decode

select product_id,
       max(decode(period,'L13',sales)) as L13_sales,
       max(decode(period,'L26',sales)) as L26_sales,
       max(decode(period,'L52',sales)) as L52_sales,
       max(decode(period,'L13',profit)) as L13_profit,
       max(decode(period,'L26',profit)) as L26_profit,
       max(decode(period,'L52',profit)) as L52_profit
from t
group by product_id;