使用 SQL Server 進行 PIVOT

我有返回以下數據的 SQL Server 表數據。（綠色文字是“結果”列中每個單元格的值的註釋）

我想 PIVOT 該查詢結果，以便它對這些相同的文件名使用最新的“ReceivedTime”。並且結果值應該分成單獨的列 - 每次出現一個 COUNT。

到目前為止，這是我的程式碼。但是，這會返回多行而不是按文件名分組。我需要改變什麼？

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; 

您需要添加一些聚合：

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]; 

引用自：https://dba.stackexchange.com/questions/211173