2

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

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]; 
  • Get this error: "Column 'PivotTable.-1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." – Kevin Jul 2 '18 at 17:33
  • It runs successfully for me, check for missing comma/semicolon in wrong place? – Gareth Lyons Jul 2 '18 at 17:38
  • 1
    @Kevin Did you wrap the [-1] in an aggregate as well? – mathewb Jul 2 '18 at 17:40
  • @Kevin Also you are using a 10-year old database platform, is it possible it's still set to work with syntax from an 18-year old database platform? Check the compatibility level and make sure it's not 80. If it is, don't just change it willy-nilly, you could restore a backup and update the compat level there, then try this same syntax. If it works you may have to consider all the testing involved with changing it on the primary database. I talked about several impacts of the 80 compat level here: dba.stackexchange.com/q/44908/1186 – Aaron Bertrand Jul 2 '18 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.