SQL/Presto: Transpose a table

Question

I have the following query, which counts the number of the pet, and display each type of pet in a row.

   with animal_table as (
    SELECT
        COUNT(*) AS total_count,
        COUNT_IF(any_match(my_array, x -> x LIKE '%dog%')) AS dog_count,
        COUNT_IF(any_match(my_array, x -> x LIKE '%cat%')) AS cat_count,
        COUNT_IF(any_match(my_array, x -> x LIKE '%pig%')) AS pig_count,
        COUNT_IF(any_match(my_array, x -> x LIKE '%duck%')) AS duck_count
    FROM my_table
    )


select 'dog' as pet, dog_count as pet_nums from animal_table
UNION ALL 
select 'cat' as pet, cat_count as pet_nums from animal_table
UNION ALL 
select 'pig' as pet, pig_count as pet_nums from animal_table
UNION ALL 
select 'duck' as pet, duck_count as pet_nums from animal_table

I use the UNION ALL to transpose each pet to a row. However, this is not scalable when the type of pets grow. I am wondering is there a better way to do this (table transpose)? Thank you!

Answer 1

Simply use group by and unnest():

select pet, count(*)
from my_table t cross join
     unnest(t.my_array) u(pet)
group by pet;

If you want to count rows only once, then use a primary key or:

select pet, count(distinct seqnum)
from (select t.*, row_number() over (order by <some column>) as seqnum
      from my_table t
     ) t cross join
     unnest(t.my_array) u(pet)
group by pet;