SQL/Presto: Transpose a table

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!

from Recent Questions - Stack Overflow https://ift.tt/3kZJZrV
https://ift.tt/eA8V8J