Building a custome pivot using subqueries

Question

I'm trying to build my own custome pivot view where i can use multiple aggregations and more than one column to filter values on. To do that i tried to get an simple example up and running to understand how to do that. Unfortunately i'm running in an limitation i don't know how to move around. I have to admin that im pretty new to Snowflake and this might be solvable by the existing pivot function that i just don't understand right to use.

First of all let my give you an example what i want to achive: given a Table like so:

create temporary table test_table(PLACE varchar, TYPE varchar, METER Int, PRICE Int);
insert overwrite into test_table values
    ('Place_A', 'flat', 1, 10),
    ('Place_A', 'flat', 2, 11),
    ('Place_A', 'house', 3, 12),
    ('Place_A', 'house', 4, 13),
    ('Place_B', 'flat', 5, 14),
    ('Place_B', 'flat', 6, 15),
    ('Place_B', 'flat', 7, 16),
    ('Place_B', 'house', 8, 17),
    ('Place_B', 'house', 9, 18),
    ('Place_B', 'house', 10, 19);

i want to produce the following:

PLACE	FLAT_AVG	FLAT_MAX	HOUSE_AVG	HOUSE_MAX	And More ...
Place_A	1.5	11	3.5	13	...
Place_B	6.0	16	9.0	19	...

By using something like that

With 
    "AGG" As (Select PLACE, TYPE, AVG(METER) AS "AVG(METER)", MAX(PRICE) AS "MAX(PRICE)", COUNT(PRICE) From test_table Group By PLACE, TYPE)
Select 
    PLACE, 
    (Select "AVG(METER)" FROM "AGG" Where PLACE = "O".PLACE AND TYPE = 'flat') AS "FLAT_AVG",
    (Select "MAX(PRICE)" FROM "AGG" Where PLACE = "O".PLACE AND TYPE = 'flat') AS "FLAT_MAX",
    (Select "AVG(METER)" FROM "AGG" Where PLACE = "O".PLACE AND TYPE = 'house') AS "HOUSE_AVG",
    (Select "MAX(PRICE)" FROM "AGG" Where PLACE = "O".PLACE AND TYPE = 'house') AS "HOUSE_MAX"
  FROM test_table as "O" Group By PLACE

The problem is that "O".PLACE seams not to work there. I just get an Error saying:

SQL compilation error: Unsupported subquery type cannot be evaluated

I tried something similar in mariadb where a subquery like that would to the trick. I wonder what i have to do in snowflake to get something like that running?

I also tried the pivot function like so:

With 
    "P1" as (select * 
        from (Select PLACE, TYPE, METER From test_table)
        pivot(AVG(METER) for TYPE in ('flat', 'house'))
        as p1 (PLACE, FLAT_AVG, HOUSE_AVG)),
    "P2" as (select * 
        from (Select PLACE, TYPE, PRICE From test_table)
        pivot(MAX(PRICE) for TYPE in ('flat', 'house'))
        as p1 (PLACE, FLAT_MAX, HOUSE_MAX))

Select "P1".PLACE, 
FLAT_AVG, FLAT_MAX,
HOUSE_AVG, HOUSE_MAX
From "P1" 
inner join "P2" on "P1".PLACE = "P2".PLACE

This did the trick for my example but like i sad in the beginning i want more than on column to filter on. How would i expand that if there was a second Column like TYPE? I have a clear idea how to do that with my first approach but not with the pivot function.

Let me know what you think i can try next.

Thanks for reading and trying to help!

user_5070879 · Answer

SQL compilation error: Unsupported subquery type cannot be evaluated
I tried something similar in mariadb where a subquery like that would to the trick. I wonder what i have to do in snowflake to get something like that running?

Resolving correlated subquery with conditional aggregation:

With AGG As (
    Select PLACE
         ,TYPE
         ,AVG(METER) AS "AVG(METER)"
         ,MAX(PRICE) AS "MAX(PRICE)"
         ,COUNT(PRICE)
    From test_table 
    Group By PLACE, TYPE
)
Select 
    O.PLACE
    ,MAX(IFF(A.TYPE='flat', "AVG(METER)", NULL)) AS FLAT_AVG
    ,MAX(IFF(A.TYPE='flat', "MAX(PRICE)", NULL)) AS FLAT_MAX
    ,MAX(IFF(A.TYPE='house', "AVG(METER)", NULL)) AS HOUSE_AVG
    ,MAX(IFF(A.TYPE='house', "MAX(PRICE)", NULL)) AS HOUSE_MAX
FROM test_table as O
LEFT JOIN AGG A  
  ON A.PLACE = O.PLACE
GROUP BY O.PLACE;

db<>fiddle demo MariaDB

Building a custome pivot using subqueries

1 Answers