[SOLVED] How to improve CASE WHEN statement to return result on same line

How to improve CASE WHEN statement to return result on same line

Currently working on some SQL script from a OpenEdge progress database. At the moment i am trying to return time for an event based on if it was Estimated or Actual. This can be identified via a result in a column. The problem i get is the way the data work is it shows the same EveNumber but on two lines (one for estimated one for actual). I would like to combine these rows into one.

I have tried the code below but this gets me the result still of two separate lines. I tried putting the case when also into the Else section but this didn't work. Below is my condensed code:

SELECT 

Event_0.EveNumber
, (CASE WHEN Object_0.ObjName = '818 Artwork typeset duration' THEN TemplateRunObject_0.TroValue ELSE NULL END) AS 'Actual'
, (CASE WHEN Object_0.ObjName = '818 Artwork Estimated typeset duration' THEN TemplateRunObject_0.TroValue ELSE NULL END) AS 'Estimated'

FROM 
SBS.PUB.Event Event_0
LEFT JOIN SBS.PUB.TemplateRunObject TemplateRunObject_0 ON Event_0.TemplateRunID = TemplateRunObject_0.TemplateRunID 
JOIN SBS.PUB.Object Object_0 ON TemplateRunObject_0.ObjectId = Object_0.ObjectId

Orginal Data layout

Evenumber             Ogjname                              TroValue
123          818 Artwork typeset duration                     15
123          818 Artwork Estimated typeset duration           30

Currently the results look like this:

EveNumber          Actual          Estimated
123                 15                0
123                  0                30

How i want them to appear

EveNumber          Actual          Estimated
123                 15                30

Solution

use max() and group by

SELECT 

Event_0.EveNumber
, max((CASE WHEN Object_0.ObjName = '818 Artwork typeset duration' THEN TemplateRunObject_0.TroValue ELSE NULL END) ) AS 'Actual'
,max( (CASE WHEN Object_0.ObjName = '818 Artwork Estimated typeset duration' THEN TemplateRunObject_0.TroValue ELSE NULL END) )AS 'Estimated'

FROM 
SBS.PUB.Event Event_0
LEFT JOIN SBS.PUB.TemplateRunObject TemplateRunObject_0 ON Event_0.TemplateRunID = TemplateRunObject_0.TemplateRunID 
JOIN SBS.PUB.Object Object_0 ON TemplateRunObject_0.ObjectId = Object_0.ObjectId
group by  Event_0.EveNumber