SQL (Redshift) SELECT COUNT from CSV column

Question

Hi exchange community,

Problem statement and details:
I am trying to query a column that contains CSV data to produce a count of instances of each value in the table. There are a total of 14 possible values in the CSV data. These are static and will never change. Each record contains at least one value in the CSV column but may contain up to all 14 values. Each value may only appear at most once in the CSV column per record.

Sample Data from Pets column:
cat, dog fish, rat
dog
dog, cat horse, ferret, zebra
dog, cat, alligator, bear, fish, bird

what have i tried?
I've gotten as far as being able to return a view that contains a single record with columns containing a count for the number of instances. It's close to what I need, I just need to have this transposed (rows <-> columns). any ideas?

SELECT 
    count(case when vet.pets like '%dog%' THEN 1 ELSE NULL END) AS dog
    ,  count(case when vet.pets like '%cat%' THEN 1 ELSE NULL END) as cat
    ,  count(case when vet.pets like '%rat%' THEN 1 ELSE NULL END) as rat
    ,  count(case when vet.pets like '%bird%' THEN 1 ELSE NULL END) as bird
    ,  count(case when vet.pets like '%fish%' THEN 1 ELSE NULL END) as fish
    ,  count(case when vet.pets like '%hamster%' THEN 1 ELSE NULL END) as hamster
    ,  count(case when vet.pets like '%ferret%' THEN 1 ELSE NULL END) as ferret
    ,  count(case when vet.pets like '%horse%' THEN 1 ELSE NULL END) as horse
    ,  count(case when vet.pets like '%zebra%' THEN 1 ELSE NULL END) as zebra
    ,  count(case when vet.pets like '%bear%' THEN 1 ELSE NULL END) as bear
    ,  count(case when vet.pets like '%alligator%' THEN 1 ELSE NULL END) as alligator
    ,  count(case when vet.pets like '%sugar_glider%' THEN 1 ELSE NULL END) as sugar_glider
    ,  count(case when vet.pets like '%tiger%' THEN 1 ELSE NULL END)  as tiger
    ,  count(case when vet.pets like '%rock%' THEN 1 ELSE NULL END) as rock
    FROM vet

This produces:

dog | cat | rat | bird | fish | hamster | ferret | horse | zebra | bear | alligator | sugar_glider | tiger | rock
3 | 5 | 2 | 6 | 8 | 1 | 8 | 5 | 1 | 3 | 2 | 2 | 4 | 2

But I need it in the format:

dog | 3
cat | 5
rat | 2
bird | 6
fish | 8
hamster | 1
ferret | 8
horse | 5
zebra | 1
bear | 3
alligator | 2
sugar_glider | 2
tiger | 4
rock | 2

Answer 1

Was able to figure this out by unioning my individual count statements.

SELECT 'dog' as 'animal', count(case when vet.pets like '%dog%' THEN 1 ELSE NULL END) AS 'count' FROM vet
UNION ALL
SELECT 'cat' as 'animal',  count(case when vet.pets like '%cat%' THEN 1 ELSE NULL END) AS 'count' FROM vet
UNION ALL
etc.