Want to use multiple aggregate function with snowflake pivot columns function-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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)
;