Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Pivot with multiple aggregate function calls?

TexasApexDeveloperDec 8 2014 — edited Dec 9 2014

Hi,

  Using Oracle 11g R2, with APEX 4.2.3, I am trying to take the following cross tab (pivot) built query:

begin
return 'with pivot_data as (
                                                SELECT department_id, job_id, salary
                                                FROM oehr_employees
                                              )
SELECT *
FROM pivot_data
PIVOT (
SUM(salary)
FOR department_id
IN ('||v('P5_DEPT_LIST')||')
)';
end;

Which works just fine, and add in the comm column and also have the pivot sum and build department_id based columns..  I haven't found any documentation showing how to have multiple columns summed in a pivot query yet.

Any ideas??

Thank you,

Tony Miller
LuvMuffin Software
Ruckersville, VA
This post has been answered by odie_63 on Dec 9 2014
Jump to Answer

Comments

Frank Kulash

Hi,

It's not clear what you want.  It would help if you posted a little sample data (CREATE TABLE and INSERT statements) and the exact results you want from that sample data.

If you want to SUM, or do any kind of aggregate function, on multiple columns, here's one way:

WITH    pivot_data    AS


(


    SELECT  job, sal, comm, deptno


    FROM    scott.emp


)


SELECT    *


FROM      pivot_data


PIVOT     (    SUM (sal)      AS sal


          ,    SUM (comm)     AS comm


          ,    AVG (sal)      AS avgsal


          FOR  job  IN ( 'ANALYST'    AS analyst


                       , 'CLERK'      AS clerk


                       , 'MANAGER'    AS mannager


                       , 'PRESIDENT'  AS president


                       , 'SALESMAN'   AS salesman


                       )


          )


ORDER BY  deptno


;

See the Forum FAQ:

odie_63
Answer 
TexasApexDeveloper wrote:




I haven't found any documentation showing how to have multiple columns summed in a pivot query yet.

Not even THE official documentation?

http://docs.oracle.com/cd/E11882_01/server.112/e25554/analysis.htm#DWHSG8731

Marked as Answer by TexasApexDeveloper · Sep 27 2020
TexasApexDeveloper

My bad, I should have done further research.. That looks spot on!!

Thank you,

Tony Miller
LuvMuffin Software
Ruckersville, VA

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 6 2015
Added on Dec 8 2014
#pivot
3 comments
5,581 views