Want to use multiple aggregate function with snowflake pivot columns function

Question

CREATE TABLE person (id INT, name STRING, date date, class INT, address STRING);
INSERT INTO person VALUES
    (100, 'John', 30-1-2021, 1, 'Street 1'),
    (200, 'Mary', 20-1-2021, 1, 'Street 2'),
    (300, 'Mike', 21-1-2021, 3, 'Street 3'),
    (100, 'John', 15-5-2021, 4, 'Street 4');

SELECT * FROM person
    PIVOT (
        **SUM(age) AS a, MAX(date) AS c**
        FOR name IN ('John' AS john, 'Mike' AS mike)
    );

This is databricks sql code above, how do I implement the same logic in snowflake

There is no "age" column in your table. How are the data to be returned? In the case of pivot, it's a bit weird. Can you show the result of such query? — Michael Golos, Nov 26 '21 at 15:07
There are several example methods of multiple column 'pivot' here (https://stackoverflow.com/questions/69017226/how-to-pivot-multiple-aggregation-in-snowflake/69087117#69087117). If you can correct your DDL/SQL for the missing AGE column and provide an example result with the expected output, we can adapt one of these methods to your use-case. — Fieldy, Nov 28 '21 at 12:04

score 0 · Answer 1 · answered Nov 26 '21 at 10:39

Below is the syntax for PIVOT in Snowflake:

SELECT ...
FROM ...
   PIVOT ( <aggregate_function> ( <pivot_column> )
            FOR <value_column> IN ( <pivot_value_1> [ , <pivot_value_2> ... ] ) )

[ ... ]

In case of Snowflake, your AS keyword will be outside the PIVOT function.

Check this example for your reference:

select * 
  from monthly_sales
    pivot(sum(amount) for month in ('JAN', 'FEB', 'MAR', 'APR'))
      as p
  order by empid;

Visit this official document and check the given examples for better understanding.

score 0 · Answer 2 · answered Nov 26 '21 at 23:34

Firstly, there is no "AGE" column as I can see from your table DDL.

Secondly, I do not think you can pivot on multiple aggregation functions, as the value will be put under the mentioned columns "JOHN" and "MIKE" for their corresponding aggregated values, it can't fit into two separate values. I don't know how your DataBricks example would work.

Your example will look something like below in Snowflake, after removing one aggregation function:

SELECT * 
FROM 
    person 
    PIVOT (
        MAX(date) FOR name IN ('John', 'Mike')
    )
    as p (id, class, address, john, mike)
;

score 0 · Answer 3 · answered Dec 05 '21 at 04:28

Snowflake does not support multiple aggregate expressions in the PIVOT

And as noted by others, your AGE is missing, and you also do not have a ORDER BY clause, which makes rolling your own SQL harder.

SELECT 
    SUM(IFF(name='John',age,null)) AS john_sum_age,
    MAX(IFF(name='John',date,null)) AS john_max_date,
    SUM(IFF(name='Mike',age,null)) AS mike_age,
    MAX(IFF(name='Mike',date,null)) AS mike_max_date
FROM person

if you had the ORDER BY in your example it would become the GROUP BY clause in this form

SELECT 
    <gouping_columns>,
    SUM(IFF(name='John',age,null)) AS john_sum_age,
    MAX(IFF(name='John',date,null)) AS john_max_date,
    SUM(IFF(name='Mike',age,null)) AS mike_age,
    MAX(IFF(name='Mike',date,null)) AS mike_max_date
FROM person 
GROUP BY <gouping_columns>

Want to use multiple aggregate function with snowflake pivot columns function

3 Answers3