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?Try something like this:

select state
,isnull((select count(*) from tblFormData where state=a.state and [Question 1]='cat' ),0) as 'cat'
,--repeat the same for anothers
from tblFormData a
group by state|||How About:

USE Northwind
GO

CREATE TABLE myTable99 (Col1 char(2), Col2 varchar(10))
GO

INSERT INTO myTable99 (Col1, Col2)
SELECT 'TX', 'cat' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'dog' UNION ALL
SELECT 'CA', 'bunny' UNION ALL
SELECT 'AK', 'bunny' UNION ALL
SELECT 'AK', 'dog'
GO

SELECT driver.Col1, COUNT(b.Col2) AS Bunnies, COUNT(c.Col2) AS Cats, COUNT(d.Col2) AS Dogs
FROM (SELECT DISTINCT Col1 FROM myTable99) AS driver
LEFT JOIN myTable99 b
ON driver.Col1 = b.Col1
AND b.Col2 = 'bunny'
LEFT JOIN myTable99 c
ON driver.Col1 = c.Col1
AND c.Col2 = 'cat'
LEFT JOIN myTable99 d
ON driver.Col1 = d.Col1
AND d.Col2 = 'dog'
GROUP BY driver.col1
GO

DROP TABLE myTable99
GO|||Originally posted by Brett Kaiser
How About:

USE Northwind
GO

CREATE TABLE myTable99 (Col1 char(2), Col2 varchar(10))
GO

INSERT INTO myTable99 (Col1, Col2)
SELECT 'TX', 'cat' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'dog' UNION ALL
SELECT 'CA', 'bunny' UNION ALL
SELECT 'AK', 'bunny' UNION ALL
SELECT 'AK', 'dog'
GO

SELECT driver.Col1, COUNT(b.Col2) AS Bunnies, COUNT(c.Col2) AS Cats, COUNT(d.Col2) AS Dogs
FROM (SELECT DISTINCT Col1 FROM myTable99) AS driver
LEFT JOIN myTable99 b
ON driver.Col1 = b.Col1
AND b.Col2 = 'bunny'
LEFT JOIN myTable99 c
ON driver.Col1 = c.Col1
AND c.Col2 = 'cat'
LEFT JOIN myTable99 d
ON driver.Col1 = d.Col1
AND d.Col2 = 'dog'
GROUP BY driver.col1
GO

DROP TABLE myTable99
GO



USE Northwind
GO

CREATE TABLE myTable99 (Col1 char(2), Col2 varchar(10))
GO

INSERT INTO myTable99 (Col1, Col2)
SELECT 'TX', 'cat' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'dog' UNION ALL
SELECT 'CA', 'bunny' UNION ALL
SELECT 'AK', 'bunny' UNION ALL
SELECT 'AK', 'dog'
GO

-------
Howabout this ?
-------

Select Col1 as State,
Sum (Case when Col2='Cat' then 1 else 0 end) as Cat,
Sum (Case when Col2='Dog' then 1 else 0 end) as Dog,
Sum (Case when Col2='Bunny' then 1 else 0 end) as Bunny
from myTable99
Group By Col1|||I hope 'jiggle it' has to be happy ;)|||there's always more than one way to skin the cat/dog/bunny ;)