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;

Answer 2

Heres an alternative form using OBJECT_AGG with LATERAL FLATTEN that avoids the potential support issue of PIVOT with ARRAY_AGG proposed by Adrian White.

This should work for any aggregates on multiple input columns included within the initial ARRAY_CONSTRUCT in the OBJ_TALL CTE. I expect that the conditional aggregation option with CASE statements would be faster but you'd need to test at scale to see.

-- OBJECT FORM USING LATERAL FLATTEN 
WITH CTE AS(
                   SELECT 'X1' PRODUCT_ID,'L13' PERIOD,100  SALES,10   PROFIT
             UNION SELECT 'X1' PRODUCT_ID,'L26' PERIOD,200  SALES,20   PROFIT
             UNION SELECT 'X1' PRODUCT_ID,'L52' PERIOD,300  SALES,30   PROFIT
             UNION SELECT 'X2' PRODUCT_ID,'L13' PERIOD,500  SALES,110  PROFIT
             UNION SELECT 'X2' PRODUCT_ID,'L26' PERIOD,600  SALES,120  PROFIT
             UNION SELECT 'X2' PRODUCT_ID,'L52' PERIOD,700  SALES,130  PROFIT
             UNION SELECT 'X2' PRODUCT_ID,'L52' PERIOD,1700 SALES,1130 PROFIT)
,OBJ_TALL AS (  SELECT PRODUCT_ID, 
                OBJECT_CONSTRUCT(PERIOD,
                                 ARRAY_CONSTRUCT(  SUM(SALES)
                                                  ,SUM(PROFIT)
                                                )
                                 ) S 
                  FROM CTE 
              GROUP BY PRODUCT_ID, PERIOD)
 SELECT * FROM OBJ_TALL;
,OBJ_WIDE AS (  SELECT PRODUCT_ID, OBJECT_AGG(KEY,VALUE) OA 
                  FROM OBJ_TALL, LATERAL FLATTEN(INPUT => S) 
              GROUP BY PRODUCT_ID)
-- SELECT * FROM OBJ_WIDE;
SELECT 
    PRODUCT_ID
    ,OA:L13[0] SALES_L13 
    ,OA:L13[1] PROFIT_L13 
    ,OA:L26[0] SALES_L26 
    ,OA:L26[1] PROFIT_L26 
    ,OA:L52[0] SALES_L52 
    ,OA:L52[1] PROFIT_L52 
FROM OBJ_WIDE
ORDER BY 1;

For easy comparison to the above, heres Adrians ARRAY_AGG and PIVOT version reformatted using CTE's.

-- ARRAY FORM - RE-WRITTEN WITH CTES FOR CLARITY AND COMPARISON TO OBJECT FORM
WITH CTE AS(
                   SELECT 'X1' PRODUCT_ID,'L13' PERIOD,100  SALES,10   PROFIT
             UNION SELECT 'X1' PRODUCT_ID,'L26' PERIOD,200  SALES,20   PROFIT
             UNION SELECT 'X1' PRODUCT_ID,'L52' PERIOD,300  SALES,30   PROFIT
             UNION SELECT 'X2' PRODUCT_ID,'L13' PERIOD,500  SALES,110  PROFIT
             UNION SELECT 'X2' PRODUCT_ID,'L26' PERIOD,600  SALES,120  PROFIT
             UNION SELECT 'X2' PRODUCT_ID,'L52' PERIOD,700  SALES,130  PROFIT
             UNION SELECT 'X2' PRODUCT_ID,'L52' PERIOD,1700 SALES,1130 PROFIT)
,ARR_TALL AS (SELECT PRODUCT_ID, 
                     PERIOD,
                     ARRAY_CONSTRUCT( SUM(SALES)
                                     ,SUM(PROFIT)
                                    ) S 
                FROM CTE GROUP BY 1,2)
,ARR_WIDE AS (SELECT * 
                FROM ARR_TALL PIVOT (ARRAY_AGG(S) FOR PERIOD IN ('L13','L26','L52')  )  )
SELECT 
    PRODUCT_ID
    ,"'L13'"[0][0] SALES_L13 
    ,"'L13'"[0][1] PROFIT_L13 
    ,"'L26'"[0][0] SALES_L26 
    ,"'L26'"[0][1] PROFIT_L26 
    ,"'L52'"[0][0] SALES_L52 
    ,"'L52'"[0][1] PROFIT_L52 
FROM ARR_WIDE
ORDER BY 1;

Answer 3

I believe you can only have one pivot at one time but you can check by running the first code below. Then you can run separately only with one pivot to see if it is working fine. Unfortunately, if multiple pivots are not allowed i.e first code then you can use the third code i.e case when method OR use union first to combine them i.e (Phil Culson method from above).

 select * 
      from [table name]
        pivot(sum(amount) for PERIOD in (L13, L26, L52)),
        pivot(sum(profit) for PERIOD in (L13, L26, L52))
      order by product_id;

if the above one doesn't work try with one for example: https://count.co/sql-resources/snowflake/pivot-tables

  select * 
      from [table name]
        pivot(sum(amount) for PERIOD in (L13, L26, L52))
      order by product_id;

Otherwise you will have to apply the manual case when logic:

select 
product_id,
sum(case when Period = 'L13' then Sales end)  as sales_l13,
sum(case when Period = 'L26' then Sales end)  as  sales_l26,
sum(case when Period = 'L52' then Sales end)  as  sales_l52,
sum(case when Period = 'L13' then Profit end) as  profi_l13,
sum(case when Period = 'L26' then Profit end) as  profit_l26,
sum(case when Period = 'L52' then Profit end) as  profit_l52
from [table name]
group by 1 

Answer 4

I'm not 100% happy with this answer ... pretty sure someone can improve on this approach.

Basically PIVOTING an ARRAY ... the list of aggregation functions available to an ARRAY is not huge ... there's just one ARRAY_AGG. And PIVOT only supposed to support AVG, COUNT, MAX, MIN, and SUM. So this shouldn't work ... it does as I think PIVOT just requires an aggregation of some sorts.

I'd recommend aggregating your metrics PRIOR to constructing the ARRAY ... but does let you pivot multiple Metrics at once - which from reading Stack Overflow shouldn't be possible!

Copy|Paste|Run| .. and IMPROVE please :-)

WITH CTE AS( SELECT 'X1' PRODUCT_ID,'L13' PERIOD,100 SALES,10 PROFIT
UNION SELECT 'X1' PRODUCT_ID,'L26' PERIOD,200 SALES,20 PROFIT
UNION SELECT 'X1' PRODUCT_ID,'L52' PERIOD,300 SALES,30 PROFIT
UNION SELECT 'X2' PRODUCT_ID,'L13' PERIOD,500 SALES,110 PROFIT
UNION SELECT 'X2' PRODUCT_ID,'L26' PERIOD,600 SALES,120 PROFIT
UNION SELECT 'X2' PRODUCT_ID,'L52' PERIOD,700 SALES,130 PROFIT)


SELECT 
PRODUCT_ID
,"'L13'"[0][0] SALES_L13 
,"'L13'"[0][1] PROFIT_L13 
,"'L26'"[0][0] SALES_L26 
,"'L26'"[0][1] PROFIT_L26 
,"'L52'"[0][0] SALES_L52 
,"'L52'"[0][1] PROFIT_L52 
FROM 
(SELECT * FROM 
   (
   SELECT PRODUCT_ID, PERIOD,ARRAY_CONSTRUCT(SALES,PROFIT) S FROM CTE)
   PIVOT (ARRAY_AGG(S) FOR PERIOD IN ('L13','L26','L52')
   ) 
 )  

Example with aggregations (added 1700,1130 to L52 X2)

WITH CTE AS(
  SELECT 'X1' PRODUCT_ID,'L13' PERIOD,100  SALES,10   PROFIT
UNION SELECT 'X1' PRODUCT_ID,'L26' PERIOD,200  SALES,20   PROFIT
UNION SELECT 'X1' PRODUCT_ID,'L52' PERIOD,300  SALES,30   PROFIT
UNION SELECT 'X2' PRODUCT_ID,'L13' PERIOD,500  SALES,110  PROFIT
UNION SELECT 'X2' PRODUCT_ID,'L26' PERIOD,600  SALES,120  PROFIT
UNION SELECT 'X2' PRODUCT_ID,'L52' PERIOD,700  SALES,130  PROFIT
UNION SELECT 'X2' PRODUCT_ID,'L52' PERIOD,1700 SALES,1130 PROFIT)

SELECT 
    PRODUCT_ID
    ,"'L13'"[0][0] SALES_L13 
    ,"'L13'"[0][1] PROFIT_L13 
    ,"'L26'"[0][0] SALES_L26 
    ,"'L26'"[0][1] PROFIT_L26 
    ,"'L52'"[0][0] SALES_L52 
    ,"'L52'"[0][1] PROFIT_L52 
FROM 
   (SELECT * FROM 
   (
   SELECT PRODUCT_ID, PERIOD,ARRAY_CONSTRUCT(SUM(SALES),SUM(PROFIT)) S FROM CTE GROUP BY 1,2)
   PIVOT (ARRAY_AGG(S) FOR PERIOD IN ('L13','L26','L52')
   ) 
)  

Answer 5

Using conditional aggregation:

SELECT product_id
   ,SUM(CASE WHEN Period = 'L13' THEN Sales END) AS SALES_L13
   ,SUM(CASE WHEN Period = 'L26' THEN Sales END) AS SALES_L26
   ,SUM(CASE WHEN Period = 'L52' THEN Sales END) AS SALES_L52
   ,SUM(CASE WHEN Period = 'L13' THEN Profit END) AS PROFIT_L52
   ,SUM(CASE WHEN Period = 'L26' THEN Profit END) AS PROFIT_L52
   ,SUM(CASE WHEN Period = 'L52' THEN Profit END) AS PROFIT_L52
FROM tab
GROUP BY product_id