column1

dog,id1
cat,id1
cat,id3
dog,id1
dog,id2
cat,id3
cat,id1
dog,id2
cat,id2
dog,id3

column1    cat    dog

id1        2      2
id2        1      2
id3        2      1

CREATE TABLE #data(column1 VARCHAR(32));
INSERT INTO #data VALUES 
('dog,id1'),
('cat,id1'),
('cat,id3'),
('dog,id1'),
('dog,id2'),
('cat,id3'),
('cat,id1'),
('dog,id2'),
('cat,id2'),
('dog,id3')


SELECT
	column1,
	SUM(CASE WHEN c2 = 'cat' THEN 1 ELSE 0 END) AS cat,
	SUM(CASE WHEN c2 = 'dog' THEN 1 ELSE 0 END) AS dog
FROM
(
	SELECT
		STUFF(column1,1,CHARINDEX(',',column1),'') column1,
		LEFT(column1,CHARINDEX(',',column1)-1) c2
	FROM
		#data
)s
GROUP BY column1;

DROP TABLE #data

quote:

---

Originally posted by KidCactus

I'll go straight to my dilemma. I have a table with a column that looks something like this:

column1

dog,id1
cat,id1
cat,id3
dog,id1
dog,id2
cat,id3
cat,id1
dog,id2
cat,id2
dog,id3

I would like to do a query that would give this result in three columns:

column1    cat    dog

id1        2      2
id2        1      2
id3        2      1

I don't even know where to start, but I assume this shouldn't be that hard to obtain?

---