Sum of Unique Entries - Better Performance Than Cursor

Question

I am trying to write a query to count different groupings from a result set. I can see how to do this with the cursor, but is it possible to use the command split command? In principle, all with better performance than the cursor.

Data:

Name Entity Status --------------------------- Bob Car Broken Bob Car Broken Bob Car Fixed Bob Car Stolen Bob Bike Fixed Bob Bike Fixed Bob Bike Fixed Bob Bike Stolen Dave Scooter Broken Dave Car Broken Dave Car Fixed Dave Car Stolen 

Report:

 Name Entity Broken Fixed Stolen ------------------------------------------ Bob Car 2 1 1 Bob Bike 0 3 1 Dave Scooter 1 0 0 Dave Car 1 1 1 

thanks

Answer 1

COUNT will ignore NULL to give zero if necessary

 SELECT Name, Entity, COUNT(CASE WHEN Status ='Broken' THEN 1 ELSE NULL END) AS Broken, COUNT(CASE WHEN Status ='Fixed' THEN 1 ELSE NULL END) AS Fixed, COUNT(CASE WHEN Status ='Stolen' THEN 1 ELSE NULL END) AS Stolen FROM table_name GROUP BY Entity,Name ORDER BY Name; 

Answer 2

Use this query:

 SELECT Name , Entity , [Broken] AS Broken , [Fixed] AS Fixed , [Stolen] AS Stolen FROM ( SELECT Name , Entity , RTRIM(LTRIM(Statuse)) AS Statuse , id FROM [dbo].[table_name] AS TN ) AS T1_Temp PIVOT ( COUNT(id) FOR T1_Temp.Statuse IN ( [Broken], [Fixed], [Stolen] ) ) As PivotTable 

I think this request is faster.