CODEWITHSUNDEEP
sqloracle-databaseoracle11gpivot
mrdiu
mrdiu

Reputation: 49

Oracle SQL - Pivoting multiple rows into single column

To all the Oracle SQL pros, please, I'm trying to use a pivot to convert multiple rows into a single column. I tried looking through past posting but could not find anything related to what I want to do, or maybe I just don't know how to search for it properly. I want to take the rows HAND, PECO, CHEP and make that into 1 column called EXTRA, while leaving STORAGE and RENEWAL as their own separate columns. I'm stumped, any help would be greatly appreciated. Thanks in advance!

My current sql is, 

select * from (
select company, customer, rev_code, sum(rev_amt) amt
from revenue_anal
where company='01'
and rev_date between to_date('20\01\01','YY\MM\DD') and sysdate
group by company, customer, rev_code
order by 2,1 )
pivot (sum(amt) for rev_code in ('HAND', 'PECO', 'CHEP', 'STORAGE', 'RENEWAL'))


Query, 

COMPANY | CUSTOMER  | REV_CODE  | REV_AMT
---------------------------------------------------
01  | 101962    | HAND      | 253.377
01  | 101962    | PECO      | 60
01  | 101962    | CHEP      | 1632
01  | 101962    | STORAGE   | 2700
01  | 101962    | RENEWAL   | 60
---------------------------------------------------

Output with my current query,

COMPANY | CUSTOMER  | HAND      | PECO  | CHEP  | STORAGE   | RENEWAL
--------------------------------------------------------------------------
01      | 101962    | 253.377   | 60    | 1632  | 2700      | 60


Trying to get the output to show as 

COMPANY | CUSTOMER  | EXTRA     | STORAGE   | RENEWAL
------------------------------------------------------------------
01      | 101962    | 1945.377  | 2700      | 60

Thank you for taking the time to assist.

Upvotes: 1

Views: 670

Answers (1)

user5683823
user5683823

Reputation:

Instead of select * from ( at the very beginning of your query, write

select company, customer, hand + peco + chep as extra, storage, renewal
from   (
.........

If you expect that any of HAND, PECO or CHEP may be NULL, wrap each of them individually within NVL(...., 0) (if, in fact, NULL is to be interpreted as zero; otherwise, leave as is, and the result will be NULL if at least one of the terms is NULL).

Upvotes: 1

Related Questions