CODEWITHSUNDEEP
sqloracle-databaseoracle11gpivot
Bala S
Bala S

Reputation: 523

How to transpose the row to column in Oracle sql

My Base table like below

enter image description here

I need output like below

enter image description here

can anyone help me on this

Upvotes: 1

Views: 518

Answers (3)

Petr
Petr

Reputation: 540

This is exactly what pivot table is for:

select *
from <table>
pivot (
  sum(count)
  for type in (
    'deposit' as DEPOSIT,
    'transfer' as TRANSFER,
    'withdraw' as WITHDRAW
  )
)

Upvotes: 2

Peter &#197;
Peter &#197;

Reputation: 1319

You should be able to use something like this

SELECT Branch,
       SUM(CASE WHEN Type = 'deposit' THEN Count ELSE NULL END) Deposit,
       SUM(CASE WHEN Type = 'transfer' THEN Count ELSE NULL END) Transfer,           
       SUM(CASE WHEN Type = 'withdraw' THEN Count ELSE NULL END) Withdraw           
FROM <table_name>
GROUP BY Branch

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142743

Aggregate data, conditionally. Sample data in lines #1 - 11; query begins at line #12.

SQL> with base (type, branch, count) as
  2    (select 'deposit' , 101, 100 from dual union all
  3     select 'deposit' , 102, 150 from dual union all
  4     select 'deposit' , 103, 200 from dual union all
  5     select 'transfer', 101, 50  from dual union all
  6     select 'transfer', 102, 100 from dual union all
  7     select 'transfer', 103, 150 from dual union all
  8     select 'withdraw', 101, 25  from dual union all
  9     select 'withdraw', 102, 50  from dual union all
 10     select 'withdraw', 103, 75  from dual
 11    )
 12  select branch,
 13    sum(case when type = 'deposit'  then count end) deposit,
 14    sum(case when type = 'transfer' then count end) transfer,
 15    sum(case when type = 'withdraw' then count end) withdraw
 16  from base
 17  group by branch
 18  order by branch
 19  /

    BRANCH    DEPOSIT   TRANSFER   WITHDRAW
---------- ---------- ---------- ----------
       101        100         50         25
       102        150        100         50
       103        200        150         75

SQL>

Upvotes: 2

Related Questions