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

oracle

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

Best Answer

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 |

Related Solutions

Oracle SQL – Combine Several Rows of the Same Table into One Row

I think this will give you desired resultset :

select id,grp,itdesc,
MAX(CASE WHEN su=1 THEN DEN END) AS DEN,
MAX(CASE WHEN su=1 THEN NUM END) AS NUM,
MAX(CASE WHEN su=1 THEN SU END) AS SU ,  -- or just  "1 AS SU" 
MAX(CASE WHEN cs=1 THEN NUM END) AS CS,
MAX(CASE 
   WHEN sw=1 THEN NUM  
   WHEN sw=0 THEN 0 
   END) AS SW
FROM tab
GROUP BY 
id,grp,itdesc

How to return specific row number of a Hierachial query – Oracle

See this article from Oracle why the rownum approach won't work: http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm

See this article on StackOverflow for the solution: https://stackoverflow.com/questions/13351318/table-rownum-returns-0-records-if-rownum-is-tested-for-value-greater-than-100

Edit (for those who don't like reading): - you want to do something like this

select count(*)
from (select rownum r
        from some_table
        order by r)
where r>=101 
and rownum <= 400;

Best Answer

Related Solutions

Oracle SQL – Combine Several Rows of the Same Table into One Row

How to return specific row number of a Hierachial query – Oracle

Related Question