Sum of unique records - better performance than a cursor

Question

I am trying to write a query to count the various groupings from a resultset. I can see how to do it with a cursor, but is it possible to use the Partition By command too please? Basically, anything with better performance than a 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

score 4 · Accepted Answer · answered Jun 18 '13 at 10:03

COUNT will ignore NULLs to give zero when needed

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;

score 1 · Answer 2 · answered Sep 06 '13 at 12:19

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 query is faster.

Sum of unique records - better performance than a cursor

2 Answers2