Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

My table looks like this:

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:

0001   subapp         SubvalueA     MainValue1
0002   subapp         SubvalueB     MainValue1
0003   subapp         SubvalueA     MainValue2
share|improve this question
You can use the {} button to format your code, tables and the likes. – dezso Nov 13 '12 at 8:33

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


|  1 |      subapp |  SubvalueA |  MainValue1 |
|  2 |      subapp |  SubvalueB |  MainValue1 |
|  3 |      subapp |  SubvalueA |  MainValue2 |
share|improve this answer

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

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

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

share|improve this answer
Perfect! Thanks! – Jukka Nov 13 '12 at 7:51

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

   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');
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.