I have SQL Server table data returning the below data. (Green text is a note on the value of each cell in 'Outcome' column)
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.
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' , AS 'InProgress' , AS 'Warning' , AS 'Failed' , 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], , , , , ) ) AS PivotTable;