I am working in a Progress ODBC in Excel and I have a data set that currently holds an ID, Object type and the result for that object. The dataset looks a little like below:
EveNumber ObjName TroValue 1234 818 Artwork typeset duration 00:30:00 1234 818 Artwork Estimated typeset duration 00:40:00
I am trying to get this data to all sit on one line by using the following CASE WHEN
Select Event_0.EveNumber ,(CASE WHEN Object_0.ObjName = '818 Artwork typeset duration' THEN TemplateRunObject_0.TroValue ELSE NULL END) AS 'BookedTime' ,(CASE WHEN Object_0.ObjName = '818 Artwork Estimated typeset duration' THEN TemplateRunObject_0.TroValue ELSE NULL END) AS 'EstimatedTime'
What i am finding is that this results in a stepped result. So my returned data still comes back on multiple lines. Much like this:
EveNumber BookedTime EstimatedTime 1234 00:30:00 1234 00:40:00
Is there something i can do to return this result on the one line?
Advertisement
Answer
You need a GROUP BY
:
Select Event_0.EveNumber, MAX(CASE WHEN Object_0.ObjName = '818 Artwork typeset duration' THEN TemplateRunObject_0.TroValue ELSE NULL END) AS BookedTime, MAX(CASE WHEN Object_0.ObjName = '818 Artwork Estimated typeset duration' THEN TemplateRunObject_0.TroValue ELSE NULL END) AS EstimatedTime FROM . . . GROUP BY Event_0.EveNumber;
Note: Single quotes are used in SQL to define strings and date constants. Do not use them for column aliases. One day, that is just going to cause you problems, when you refer to a column using single quotes but it is interpreted as a string.