This, if you have just cats and dogs. But if you have hamsters and goldfish and all other kinds of exotic pets, you would need to use some kind of dynamic query perhaps, especially if the kinds of pets are unknown in advanceCREATE 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 dogFROM( SELECT STUFF(column1,1,CHARINDEX(',',column1),'') column1, LEFT(column1,CHARINDEX(',',column1)-1) c2 FROM #data)sGROUP BY column1;DROP TABLE #data