How to use subqueries in Oracle?

Question

I have the following table:

table1
-------------------------
 date      |  ID  | name
-------------------------
 13-jul-15 |   1  |  abc
 13-jul-15 |   2  |  abc
 14-jul-15 |   1  |  def
 13-jul-15 |   3  |  abc
 15-jul-15 |   3  |  def
 ...

What I want to do is match the ID and represent as below:

 date1     | name | date2     | name | ID 
------------------------------------------ 
 13-jul-15 | abc  | 14-jul-15 | def  | 1   
 13-jul-15 | abc  |           |      | 2  
 13-jul-15 | abc  | 15-jul-15 | def  | 3
 ...

I have used the following code, but not getting the result.

CREATE PROCEDURE get_details ( oresults1 OUT SYS_REFCURSOR ) AS
BEGIN
  SELECT *
    FROM ((SELECT date, ID FROM table1 WHERE name= "abc") T1
           UNION ALL
          (SELECT date, ID FROM table1 WHERE name= "def") T2 
         )
   WHERE T1.ID= T2.ID
   ORDER BY ID;
END;

What have I done wrong?

Answer 1

Here are a couple of alternatives:

with table1 as (select to_date('13/07/2015', 'dd/mm/yyyy') dt, 1 id, 'abc' name from dual union all
                select to_date('13/07/2015', 'dd/mm/yyyy') dt, 2 id, 'abc' name from dual union all
                select to_date('14/07/2015', 'dd/mm/yyyy') dt, 1 id, 'def' name from dual union all
                select to_date('13/07/2015', 'dd/mm/yyyy') dt, 3 id, 'abc' name from dual union all
                select to_date('15/07/2015', 'dd/mm/yyyy') dt, 3 id, 'def' name from dual)
-- end of mimicking your table1. See below for the query
select t1.dt date1,
       t1.name name1,
       t2.dt date2,
       t2.name name2,
       t1.id
from   table1 t1
       left outer join table1 t2 on (t1.id = t2.id and t1.name = 'abc' and t2.name = 'def')
where  t1.name = 'abc'
order by t1.id;

DATE1      NAME1 DATE2      NAME2         ID
---------- ----- ---------- ----- ----------
13/07/2015 abc   14/07/2015 def            1
13/07/2015 abc                             2
13/07/2015 abc   15/07/2015 def            3

with table1 as (select to_date('13/07/2015', 'dd/mm/yyyy') dt, 1 id, 'abc' name from dual union all
                select to_date('13/07/2015', 'dd/mm/yyyy') dt, 2 id, 'abc' name from dual union all
                select to_date('14/07/2015', 'dd/mm/yyyy') dt, 1 id, 'def' name from dual union all
                select to_date('13/07/2015', 'dd/mm/yyyy') dt, 3 id, 'abc' name from dual union all
                select to_date('15/07/2015', 'dd/mm/yyyy') dt, 3 id, 'def' name from dual)
-- end of mimicking your table1. See below for the query
select t1.dt date1,
       t1.name name1,
       t2.dt date2,
       t2.name name2,
       t1.id
from   (select id, dt, name from table1 where name = 'abc') t1
       left outer join (select id, dt, name from table1 where name = 'def') t2 on (t1.id = t2.id)
where  t1.name = 'abc'
order by t1.id;

DATE1      NAME1 DATE2      NAME2         ID
---------- ----- ---------- ----- ----------
13/07/2015 abc   14/07/2015 def            1
13/07/2015 abc                             2
13/07/2015 abc   15/07/2015 def            3