Wednesday, March 7, 2012

Columnar Data with Group By

If I had the following table

tblFormData

State Question 1
-- ----
TX......cat
TX......dog
TX......dog
CA......cat
CA......cat
CA......dog
CA......bunny
AK......bunny
AK......dog

and I want the results to look like this

State cat dog bunny
----------
TX......1......2......0
CA......2......1......1
AK......0......1......1

How would I do this? Ignore the ......, i had to use them because this board erases my spaces...Something like:


Select
State,
Sum(CASE WHEN Question = 'cat' THEN 1 ELSE 0 END) as cat,
Sum(CASE WHEN Question = 'dog' THEN 1 ELSE 0 END) as dog,
Sum(CASE WHEN Question = 'bunny' THEN 1 ELSE 0 END) as bunny
FROM tblFormData
Group BY State

No comments:

Post a Comment