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 Command, can the IN clause be dynamic rather than hard-coded values?

Ericg-OracleFeb 23 2011 — edited Feb 23 2011
I want to have a number of sql statements that uses the PIVOT command be able to run dynamically without having to hard-code the values I want selected within the 'IN' clause. Here is a simple example.

REGION_ID SALES_MONTH NUM_SALES

1 02/01/2010 12
1 03/01/2010 11
1 04/01/2010 10
2 02/01/2010 22
2 03/01/2010 21
2 04/01/2010 20

The PIVOT command I used is as follows:

select * from
(select * from sales)
PIVOT
(
sum(num_sales)
for sales_month in ('02/01/2010','03/01/2010','04/01/2010'))

This works fine for now, but as new months are created, I don't want to have to redo all my sql statements each month. Any suggestions on how I can do this?
This post has been answered by Solomon Yakobson on Feb 23 2011
Jump to Answer

Comments

Solomon Yakobson
Answer
No, unless you use XML pivoting:
select  *
  from  (
         select  job,
                 deptno,
                 sal
           from  emp
        ) pivot xml(sum(sal) for job in (select distinct job from emp))
/

    DEPTNO JOB_XML
---------- ------------------------------------------------------------
        10 <PivotSet><item><column name = "JOB">ANALYST</column><column
            name = "SUM(SAL)"></column></item><item><column name = "JOB
           ">CLERK</column><column name = "SUM(SAL)">1300</column></ite
           m><item><column name = "JOB">MANAGER</column><column name =
           "SUM(SAL)">2450</column></item><item><column name = "JOB">PR
           ESIDENT</column><column name = "SUM(SAL)">5000</column></ite
           m><item><column name = "JOB">SALESMAN</column><column name =
            "SUM(SAL)"></column></item></PivotSet>

        20 <PivotSet><item><column name = "JOB">ANALYST</column><column
            name = "SUM(SAL)">6000</column></item><item><column name =
           "JOB">CLERK</column><column name = "SUM(SAL)">1900</column><
           /item><item><column name = "JOB">MANAGER</column><column nam
           e = "SUM(SAL)">2975</column></item><item><column name = "JOB
           ">PRESIDENT</column><column name = "SUM(SAL)"></column></ite
           m><item><column name = "JOB">SALESMAN</column><column name =
            "SUM(SAL)"></column></item></PivotSet>

        30 <PivotSet><item><column name = "JOB">ANALYST</column><column
            name = "SUM(SAL)"></column></item><item><column name = "JOB
           ">CLERK</column><column name = "SUM(SAL)">950</column></item
           ><item><column name = "JOB">MANAGER</column><column name = "
           SUM(SAL)">2850</column></item><item><column name = "JOB">PRE
           SIDENT</column><column name = "SUM(SAL)"></column></item><it
           em><column name = "JOB">SALESMAN</column><column name = "SUM
           (SAL)">5600</column></item></PivotSet>


SQL>
However, if you want it in "readable" format you still need to know number of returned columns:
with t as (
           select * from (
                          select  job,
                                  deptno,
                                  sal
                            from  emp
                        ) pivot xml(sum(sal) for job in (select distinct job from emp))
          )
select  deptno,
        extractvalue(job_xml,'/PivotSet/item[1]/column[2job1,
        extractvalue(job_xml,'/PivotSet/item[2]/column[2job2,
        extractvalue(job_xml,'/PivotSet/item[3]/column[2job3,
        extractvalue(job_xml,'/PivotSet/item[4]/column[2job4,
        extractvalue(job_xml,'/PivotSet/item[5]/column[2job5
  from  t
/

    DEPTNO JOB1       JOB2       JOB3       JOB4       JOB5
---------- ---------- ---------- ---------- ---------- ----------
        10            1300       2450       5000
        20 6000       1900       2975
        30            950        2850                  5600

SQL>
SY.
Marked as Answer by Ericg-Oracle · Sep 27 2020
2 people found this helpful
Ericg-Oracle
Thanks, very helpful. A little followup -- so even if I hard-code future values into the IN statement, I can't seem to block the future months from being displayed, correct?

select * from
(select * from sales where sales_month in ('02/01/2010','03/01/2010','04/01/2010'))
PIVOT
(
sum(num_sales)
for sales_month in ('02/01/2010','03/01/2010','04/01/2010','05/01/2010','06/01/2010'))

The list of months within the 'in' list seems to override the select condition by showing May and June as blank columns. Any way to work around the problem this way that you can think of?
Solomon Yakobson
user719232 wrote:

Any way to work around the problem this way that you can think of?
Not really.

SY.
Frank Kulash
Hi,

The following thread discusses various ways of handling a dynamic number of pivoted columns:
3529823

Your best bet is probably string aggregation.
1 person found this helpful
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 23 2011
Added on Feb 23 2011
#pivot, #sql
4 comments
56,446 views