SQL Server PIVOT – How to Use

pivotsql serversql-server-2008-r2t-sql

I have SQL Server table data returning the below data. (Green text is a note on the value of each cell in 'Outcome' column)

enter image description here

I want to PIVOT that query result so that it uses the latest 'ReceivedTime' for those same filenames. And the Outcome values should be split into separate columns – with a COUNT of each occurrence.

enter image description here

Here is my code so far. However, this returns multiple rows rather than grouping by filename.
What do I need to change?

DECLARE @TmpTable AS TABLE
(
    Filename varchar(80),
    ReceivedTime datetime,
    Outcome int NULL
);
INSERT INTO @TmpTable
VALUES
    ('FileABC','2018/06/29 03:34', 1),
    ('FileABC','2018/06/28 11:01', 3),
    ('FileABC','2018/06/28 09:30', 2),
    ('FileABC','2018/06/28 05:23', NULL),
    ('FileABC','2018/06/28 16:21', 3);

SELECT 
     Filename as 'LatestReceivedTime'
    ,[-1] AS 'NotProcessing'
    ,[0] AS 'InProgress'
    ,[2] AS 'Warning'
    ,[3] AS 'Failed'
    ,[1] AS 'Success'
FROM 
(
    SELECT x.Filename, x.ReceivedTime, COALESCE(x.Outcome, -1) AS 'Outcome'
    FROM @TmpTable x

) x
PIVOT
(  
COUNT(Outcome)  
FOR Outcome IN ([-1], [0], [1], [2], [3], [4])  
) AS PivotTable;

Best Answer

You need to add some aggregation:

SELECT 
     [Filename] 
    ,MAX(ReceivedTime) as 'LatestReceivedTime'
    ,SUM([-1]) AS 'NotProcessing'
    ,SUM([0]) AS 'InProgress'
    ,SUM([2]) AS 'Warning'
    ,SUM([3]) AS 'Failed'
    ,SUM([1]) AS 'Success'
FROM 
(
    SELECT x.Filename, x.ReceivedTime, COALESCE(x.Outcome, -1) AS 'Outcome'
    FROM @TmpTable x

) x
PIVOT
(  
COUNT(Outcome)  
FOR Outcome IN ([-1], [0], [1], [2], [3], [4])  
) AS PivotTable

GROUP BY [Filename];
Related Question