Oracle Sql query to return the column result in single row

Question

I have below select query:

select distinct(e.ENGINE_ID),
       e.ENGINE_NAME,
       os.OBJECT_STATUS,
       br.NAME,
       env.NAME
from ENGINE e,
     RATOR_MONITORING.OBJECT_STATUS os,
     BRAND_ENGINE be,
     ENVIRONMENT env,
     BRAND br
where e.ENGINE_ID = os.OBJECT_ID
  AND os.OBJECT_TYPE='ENGINE'
  AND be.ENGINE_ID = e.ENGINE_ID
  AND be.BRAND_ID = br.BRAND_ID 
  AND br.ENV_ID = env.ENV_ID  
order by decode(os.OBJECT_STATUS, 'R',1, 'Y', 2, 'G', 3, 'N',4) asc,
         UPPER(e.ENGINE_NAME) asc

The above query return the result as:

As you can see its returning the duplicate Engine_ID with same ENGINE_NAME,OBJECT_STATUS,NAME_1 and the NAME column has different result for the same ENGINE_ID. So i want to return the result for such records in single row. For example as mentioned below:

ENGINE_ID  ENGINE_NAME   OBJECT_STATUS  NAME      NAME_1
39         ORDER_ENGINE  G              NC,LIDL   FONIC

DISTINCT is not a function on a column, it applies to the whole selected row. (I's actually SELECT DISTINCT, as the opposite to SELECT [ALL].) select distinct(e.ENGINE_ID), e.ENGINE_NAME is the same as select distinct e.ENGINE_ID , e.ENGINE_NAME, which is the same as select distinct e.ENGINE_ID, (e.ENGINE_NAME) etc. — jarlh, Oct 12 '15 at 10:25
What is your Oracle database version up to 4 decimal places? — Lalit Kumar B, Oct 12 '15 at 10:32
There are multiple techniques of **string aggregation**. The answer depends on the Oracle database version. — Lalit Kumar B, Oct 12 '15 at 10:34
I am using Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production — Andrew, Oct 12 '15 at 10:40

score 1 · Accepted Answer · answered Oct 12 '15 at 10:26

Oracle's LISTAGG-function is your solution. http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030

select e.ENGINE_ID,
       e.ENGINE_NAME,
       os.OBJECT_STATUS,
       LISTAGG(br.NAME, ', ') WITHIN GROUP (ORDER BY br.NAME) AS NAME,
       env.NAME as NAME_1
from ENGINE e,
     RATOR_MONITORING.OBJECT_STATUS os,
     BRAND_ENGINE be,
     ENVIRONMENT env,
     BRAND br
where e.ENGINE_ID = os.OBJECT_ID
  AND os.OBJECT_TYPE='ENGINE'
  AND be.ENGINE_ID = e.ENGINE_ID
  AND be.BRAND_ID = br.BRAND_ID 
  AND br.ENV_ID = env.ENV_ID  
group by e.ENGINE_ID,
         e.ENGINE_NAME,
         os.OBJECT_STATUS,
         env.NAME
order by decode(os.OBJECT_STATUS, 'R',1, 'Y', 2, 'G', 3, 'N',4) asc,
         UPPER(e.ENGINE_NAME) asc

Oracle Sql query to return the column result in single row

1 Answers1