I have the following table structure :
Code: Select all
ParentObjectID ObjectID Status
-------------- --------- ------
1 1 1
1 2 1
1 3 2
1 4 2
1 5 3
2 6 1
Code: Select all
ParentObjectID Status1Count Status2Count Status3Count
1 2 2 1
2 1 0 0
Code: Select all
SELECT ParentObjectID, Status, count([Status]) AS [StatusCount] FROM MyTable GROUP BY [Status], ParentObjectID
Code: Select all
ParentObjectID Status StatusCount
1 1 2
1 2 2
1 3 1
2 1 1