Oracle SQL - Pivoting multiple rows into single column

Question

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.

user5683823 · Accepted Answer · 2020-03-19 20:35:52Z

1

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).

answered Mar 19, 2020 at 20:35

user5683823

Sign up to request clarification or add additional context in comments.

1 Comment

mrdiu Over a year ago

Yes! Thank you so much. After doing this, I was able to get the pivot table to show exactly as needed. Much appreciated!

Collectives™ on Stack Overflow

Oracle SQL - Pivoting multiple rows into single column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related