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

Question

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

you can use Coalesce Function refer to this link [https://www.1keydata.com/sql/sql-coalesce.html](https://www.1keydata.com/sql/sql-coalesce.html) — Rakesh, Apr 17 '19 at 08:37

score 1 · Accepted Answer · answered Apr 17 '19 at 08:40

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

score 1 · Answer 2 · answered Apr 17 '19 at 09:02

Its help you ; MAX() function . But you should forget 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

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

2 Answers2