Hello alternate neighborhood,
Drawback assertion and particulars:
I’m attempting to question a column that comprises CSV information to provide a rely of cases of every worth within the desk. There are a complete of 14 attainable values within the CSV information. These are static and can by no means change Every file comprises at the very least one worth within the CSV column however might comprise as much as all 14 values.
what have i attempted?
I’ve gotten so far as with the ability to return a view that comprises a single file with columns containing a rely for the variety of cases. It is near what I want, I simply must have this transposed (rows <-> columns). any concepts?
SELECT
rely(case when vet.pets like '%canine%' THEN 1 ELSE NULL END) AS canine
, rely(case when vet.pets like '%cat%' THEN 1 ELSE NULL END) as cat
, rely(case when vet.pets like '%rat%' THEN 1 ELSE NULL END) as rat
, rely(case when vet.pets like '%fowl%' THEN 1 ELSE NULL END) as fowl
, rely(case when vet.pets like '%fish%' THEN 1 ELSE NULL END) as fish
, rely(case when vet.pets like '%hamster%' THEN 1 ELSE NULL END) as hamster
, rely(case when vet.pets like '%ferret%' THEN 1 ELSE NULL END) as ferret
, rely(case when vet.pets like '%horse%' THEN 1 ELSE NULL END) as horse
, rely(case when vet.pets like '%zebra%' THEN 1 ELSE NULL END) as zebra
, rely(case when vet.pets like '%bear%' THEN 1 ELSE NULL END) as bear
, rely(case when vet.pets like '%alligator%' THEN 1 ELSE NULL END) as alligator
, rely(case when vet.pets like '%sugar_glider%' THEN 1 ELSE NULL END) as sugar_glider
, rely(case when vet.pets like '%tiger%' THEN 1 ELSE NULL END) as tiger
, rely(case when vet.pets like '%rock%' THEN 1 ELSE NULL END) as rock
FROM vet
This produces:
canine | cat | rat | fowl | 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
However I want it within the format:
canine | 3 cat | 5 rat | 2 fowl | 6 fish | 8 hamster | 1 ferret | 8 horse | 5 zebra | 1 bear | 3 alligator | 2 sugar_glider | 2 tiger | 4 rock | 2