Query returning data from two rows of a table in a single row

Question

My table looks like this:

ID     APPLICATION    OTHERVALUE
0001   subapp         SubvalueA
0001   mainapp        MainValue1
0002   subapp         SubvalueB
0002   mainapp        MainValue1
0003   subapp         SubvalueA
0003   mainapp        MainValue2

I need all lines of subapp, but then I also need value of OtherValue when the application is mainapp from same table where ID is the same. I'm using Oracle.

How can I build a query for this?

Expected result:

ID     APPLICATION    OTHERVALUE    OTHERVALUE2
0001   subapp         SubvalueA     MainValue1
0002   subapp         SubvalueB     MainValue1
0003   subapp         SubvalueA     MainValue2

Answer 1

You can use an aggregate function and a CASE statement to get the data into this format:

select id,
  max(case when application ='subapp' then application end) application,
  max(case when application ='subapp' then othervalue end) othervalue,
  max(case when application ='mainapp' then othervalue end) othervalue2
from yourtable
group by id;

See SQL Fiddle with demo

Result:

| ID | APPLICATION | OTHERVALUE | OTHERVALUE2 |
-----------------------------------------------
|  1 |      subapp |  SubvalueA |  MainValue1 |
|  2 |      subapp |  SubvalueB |  MainValue1 |
|  3 |      subapp |  SubvalueA |  MainValue2 |

Answer 2

The following statement should give you the wanted result. I haven't tested it!

select t1.id, t1.application, t1.othervalue, t2.othervalue
from 
    yourtable t1
    JOIN yourtable t2 ON t1.id = t2.id
where 
    t1.application = 'subapp'
    and t2.application = 'mainapp'
order by t1.id;

(Replace yourtable in the FROM clause with your table name.)

Answer 3

Hanspeter Oberlin and blueefeet both have good working solutions either of which may be preferable ⁺¹. Here is yet another solution with it's own pros and cons.

SELECT * FROM (
   SELECT ID, Application, OtherValue 
      , MIN(OtherValue) OVER (PARTITION BY ID ORDER BY Application) OtherValue2
   FROM yourtable
)
WHERE Application='subapp';  

drop table yourtable;
create table yourtable (ID Varchar2(4), Application varchar2(7), OtherValue Varchar2(10));
insert into yourtable values ('0001', 'subapp',  'SubvalueA');
insert into yourtable values ('0001', 'mainapp', 'MainValue1');
insert into yourtable values ('0002', 'subapp',  'SubvalueB');
insert into yourtable values ('0002', 'mainapp', 'MainValue1');
insert into yourtable values ('0003', 'subapp',  'SubvalueA');
insert into yourtable values ('0003', 'mainapp', 'MainValue2');
commit;