Skip to content
Advertisement

How to transform one SQL column into multiple columns on the same row?

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.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement