CODEWITHSUNDEEP
oracle-databaseoracle11g
Ali Tahir
Ali Tahir

Reputation: 31

Merge two rows into one row with one column having distinct values

I have two experiences of the same exp_seek_id based on a column named abroad which is either 'Y' or 'N'. I have calculated the sum of all abroad experiences and the sum of all domestic experiences. So far, I have successfully fetched the above results but I am getting two rows of the same PK . Is it possible to get both experiences that are ( abroad and domestic) in a single row?

So far, I am getting this :

EXP_SEEK_ID | Experience | Abroad
      146           7        Y
      146           3        N

Expected Result:

EXP_SEEK_ID | Abroad | Domestic
      146           7      3

My code :

Select exp_seek_id ,experience,
SUM( extract (year from exp_date_to)- extract (year from exp_date_from) )
From 
job_seek_experience where 
exp_seek_id = 146
Group By 
exp_seek_id,experience

Upvotes: 1

Views: 92

Answers (3)

Ramki
Ramki

Reputation: 463

use DECODE () its so easy 

drop table omc.test;
create table omc.test (
EXP_SEEK_ID  NUMBER(5),
Experience  NUMBER ( 3) , 
Abroad  CHAR(1) 
); 

insert into omc.test values ( 146, 7, 'Y' ) ;
insert into omc.test values ( 146, 3, 'N' ) ;

commit;

select EXP_SEEK_ID,  SUM(decode( Abroad,'Y',Experience))  Abroad  , SUM( decode( Abroad,'N',Experience))  Domestic
FROM omc.test
group by EXP_SEEK_ID

EXP_SEEK_ID ABROAD DOMESTIC

    146          7          3

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142720

The usual way to do that is to combine SUM + DECODE, e.g.

SQL> with job_seek_experience
  2    (exp_seek_id, experience, abroad) as
  3    (select 146, 1, 'Y' from dual union all
  4     select 146, 6, 'Y' from dual union all
  5     select 146, 3, 'N' from dual union all
  6     --
  7     select 222, 4, 'Y' from dual
  8    )
  9  select exp_seek_id,
 10         sum(decode(abroad, 'Y', experience)) abroad,
 11         sum(decode(abroad, 'N', experience)) domestic
 12  from job_seek_experience
 13  where exp_Seek_id = 146
 14  group by exp_Seek_id;

EXP_SEEK_ID     ABROAD   DOMESTIC
----------- ---------- ----------
        146          7          3

SQL>

The interesting part for you are lines 9-14 (CTE isn't that interesting, is it?).

Upvotes: 1

deniz
deniz

Reputation: 46

I will assume your final result as my input but it will be easy for you to modify the script for your situtation.

create table exp_table
(exp_seek_id number, experience number, abroad char);

insert into exp_table
values(146, 7, 'Y');
insert into exp_table
values(146, 3, 'N');



with abroad_exp as
        (select exp_seek_id, 
                experience, 
                abroad 
         from exp_table
         where abroad = 'Y'),
     domestic_exp as
        (select exp_seek_id, 
                experience, 
                abroad  
         from exp_table
         where abroad = 'N')
select /*+ parallel */
  abroad_exp.exp_seek_id,
  abroad_exp.experience as abroad,
  domestic_exp.experience as domestic
from abroad_exp,
     domestic_exp
where abroad_exp.exp_seek_id = domestic_exp.exp_seek_id

Upvotes: 1

Related Questions