主页 / dba / 问题 / 211173
Accepted
Kevin
Asked: 2018-07-03 09:05:56 +0800 CST

使用 SQL Server 进行透视

  • 772

我有返回以下数据的 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;
sql-server sql-server-2008-r2

1 个回答

  1. Best Answer

    您需要添加一些聚合：

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

相关问题