CODEWITHSUNDEEP
oracle11g
Didu
Didu

Reputation: 79

Split data in one column and convert them into column headers in ORACLE

I'm having a table like this. All I need to do is split the data in specified column into new column headers and assign matching values to those columns which are in another column.

App_No       Code     Amount

  a           rvd      100
  a           pqd      200
  b           srv      100
  b           rvd      500
  b           crb      300
  c           srv      200

and I need to select this data and convert them to a format like this.

    App_No      rvd     pqd     srv     crb

      a         100     200      0       0
      b         500      0      100     300  
      c          0       0      200      0

I need to make column headers with code data. how can I achieve this in oracle? can I do this with 'pivot'?

Upvotes: 1

Views: 65

Answers (1)

CompEng
CompEng

Reputation: 7376

TRY THIS:

select  
app_no,
nvl(rvd,0)rvd,
nvl(rqd,0)rqd,
nvl(srv,0)srv,
nvl(crb,0)crb
from
main_table 
PIVOT
(
sum(amount) for (code) in (
'rvd' as rvd,
'rqd' as rqd,
'srv' as srv,
'crb' as crb
) 
)

Upvotes: 1

Related Questions