CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Naresh
Naresh

Reputation: 81

Oracle : merge two rows values into one row with new column names

The version I'm using is Oracle database 12c.

select id,currency_code,currency_classification_code,amount from table;

DATA :

id   currency_code    currency_classification_code    Amount
1    USD              Reporting                       111111
1    AUD              BASE                            222222

Expected result :

id   Amt_in_base_curncy  base_curncy_code  Amt_in_rptng_curncy rptng_curncy_code
1    222222              AUD               111111              USD

I want the data in the above format , by creating four new columns.

please let me know if you need more details.

Please help.

Upvotes: 1

Views: 345

Answers (4)

Radagast81
Radagast81

Reputation: 3016

Exactly for this kind of problems Oracle has the PIVOT-queries:

select *
  from (select id,currency_code,currency_classification_code,amount from table)
  pivot (sum(amount) as amt_in
        ,max(currency_code) as code 
     for (currency_classification_code) in ('BASE' as base_curncy, 'Reporting' as rptng_curncy))

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

Let's go another route:

If you can't be certain that in all cases you'll have BOTH a 'BASE' and a 'Reporting' row for each ID you'll need to use a FULL JOIN to put the data together:

WITH cteBase AS (SELECT *
                   FROM YOUR_TABLE
                   WHERE CURRENCY_CLASSIFICATION_CODE = 'BASE'),
     cteReporting AS (SELECT *
                        FROM YOUR_TABLE
                        WHERE CURRENCY_CLASSIFICATION_CODE = 'Reporting')
SELECT COALESCE(b.ID, r.ID) AS ID,
       b.AMOUNT AS Amt_in_base_curncy,
       b.CURRENCY_CODE AS base_curncy_code,
       r.AMOUNT AS Amt_in_rptng_curncy,
       r.CURRENCY_CODE AS rptng_curncy_code
  FROM cteBase b
  FULL OUTER JOIN cteReporting r
    ON r.ID = b.ID

Upvotes: 1

Oto Shavadze
Oto Shavadze

Reputation: 42863

One way is conditional aggregation:

select id, 
max(case when currency_classification_code = 'BASE' then Amount end) as Amt_in_base_curncy,
max(case when currency_classification_code = 'BASE' then currency_code end) as base_curncy_code,
max(case when currency_classification_code = 'Reporting' then Amount end) as Amt_in_rptng_curncy,
max(case when currency_classification_code = 'Reporting' then currency_code end) as rptng_curncy_code
from t
group by id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use a join, for instance:

select tb.id, tb.amount, tb.currency_code, tr.amount, tr.currency_code
from t tr join
     t tb
     on tr.id = tb.id and tr.currency_classification_code = 'Reporting' and
        tb.currency_classification_code = 'BASE';

Upvotes: 1

Related Questions