CODEWITHSUNDEEP
sqloracle-database
tweant
tweant

Reputation: 307

Oracle SQL - Group by a column and extract other values to columns

My idea is to do something like this:

INPUT:

ID  CURRENCY    AMOUNT
1   RUS         14,55
1   USD         22,22
1   PLN         444,44
2   PLN         22

Then I want to group by ID and get output:

ID  CUR_1   AMOUNT_1    CUR_2   AMOUNT_2    CUR_3   AMOUNT_3
1   RUS     14,55       USD     22,22       PLN     444,44
2   PLN     22

It is important to combine the right amount with right currency. Maximal number of pairs is 3 like for an ID=1. It may vary from 1 to 3.

I tried using LISTAGG but it will generate problem with further processing of the data.

Upvotes: 0

Views: 158

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

select * 
  from (select t.*, row_number() over (partition by id order by null) rn
          from t)
  pivot (max(currency) cur, sum(amount) amt for rn in (1, 2, 3))

Test:

with t(id, currency, amount) as (
    select 1, 'RUS',  14.55 from dual union all
    select 1, 'USD',  22.22 from dual union all
    select 1, 'PLN', 444.44 from dual union all
    select 2, 'PLN',  22    from dual )
select * 
  from (select t.*, row_number() over (partition by id order by null) rn
          from t)
  pivot (max(currency) cur, sum(amount) amt for rn in (1, 2, 3))

Output:

        ID 1_CUR      1_AMT 2_CUR      2_AMT 3_CUR      3_AMT
---------- ----- ---------- ----- ---------- ----- ----------
         1 RUS        14,55 USD        22,22 PLN       444,44
         2 PLN           22

Upvotes: 3

James Quittenton
James Quittenton

Reputation: 1

You can create a virtual table for each row using a subquery, then join the virtual tables by ID into a single row.

Upvotes: 0

Related Questions