SQL & PL/SQL

No.  The SQL parser needs to know what columns to return, which is why the Pivot "in clause" needs to be static.

Here's a dynamic approach if you need it.

Hi,

user626688 wrote:
Hi I am using oracle 11g R2. Just wondering if there is a way I can PIVOT date values into columns by having a subquery within the IN clause. This way I can dynamically get the values as columns rather than punching each value in the IN clause. 
Thanks for you help!.
Regards
select * from
(select trans_date, amt from sales where trans_date between '01/01/2019' and '01/15/2019')
) pivot
(sum(amt) for trans_date in (--can we use a subquery instead of hardcoding the values)
)

The short answer is: No, sorry.

A query has to be compiled before it fetches any data.  The compiler needs to know the exact number of columns to produce, so that can't depend on any data fetched by the query.  You can use dynamic SQL, where you have 2 queries; a preliminary query that figures out how many columns, and writes part (or all) of the main query with that many columns.

You can have a fixed number of columns (15, say) and fill them with data from the last 15 days in the table, or the 15 days before today, or things like that.  That means you could hard-code just a starting date (like 01/01/2019), and have the query figure out what the next 14 dates were, and pivot those 15 columns.

XML output can have a dynamic number of columns, because the entire XML row is one XMLTYPE column.

You can do something similar using string aggregation.  That is, the output can have a huge VARCHAR2 column that is formatted so a person reading it thinks it is a variable number of separate columns.  See for an example.

Ok. Thank you!

Thanks!

user626688 wrote:
Thanks!

Then mark this thread as ANSWERED

Various people have tried, with varying success.

http://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

But make sure you are aware of the potential risks etc.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:4843682300346852395

When Tom says "This is one time when I'd almost say we have ventured into "magic"" you probably want to take a deep breath before looking to use it in production code. [https://asktom.oracle.com/pls/apex/asktom.search?tag=pivot-with-dynamic-number-of-columns-and-rows#5394721000346803830 ]

hi,

because of some limitations, you can achieve it by using 2 steps as below:

Step #1: to generate SQL statement which have Columns dynamically.... ( notice Where clause to specify date criteria )

with your_table as (Select to_date('01-dec-2019','dd-mon-yyyy') trans_date, 100 amt from dual union all
            Select to_date('02-dec-2019','dd-mon-yyyy') trans_date, 150 amt from dual union all
            Select to_date('02-dec-2019','dd-mon-yyyy') trans_date, 200 amt from dual union all
            Select to_date('03-dec-2019','dd-mon-yyyy') trans_date, 1000 amt from dual union all
            Select to_date('03-dec-2019','dd-mon-yyyy') trans_date, 500 amt from dual union all
            Select to_date('04-dec-2019','dd-mon-yyyy') trans_date, 840 amt from dual union all
            Select to_date('06-dec-2019','dd-mon-yyyy') trans_date, 840 amt from dual union all
            Select to_date('08-dec-2019','dd-mon-yyyy') trans_date, 840 amt from dual union all
            Select to_date('12-dec-2019','dd-mon-yyyy') trans_date, 840 amt from dual union all
            Select to_date('13-dec-2019','dd-mon-yyyy') trans_date, 840 amt from dual
),
t2 (in_list) as (Select listagg(''''||to_char(trans_date, 'dd-mon-yyyy')||'''',   ', ') within group (order by trans_date)
  from (Select distinct trans_date 
          from your_table
                   where trans_date between to_date('02-dec-2019','dd-mon-yyyy') 
                               and to_date('06-dec-2019','dd-mon-yyyy')
                               )
)
Select 'select *'||chr(10)||'from your_table '||chr(10)||
        'pivot(sum(amt) for trans_date in ('||chr(10)||' '|| in_list||chr(10)||'  )' ||chr(10)||' );'  as sql_str
    from t2
/

this will generate SQL statement as below to run to get data:

select *
from your_table 
pivot(sum(amt) for trans_date in (
 '02-dec-2019', '03-dec-2019', '04-dec-2019', '06-dec-2019'
  )
 );

Step #2: now run this generated SQL.  ( in step #1 you can modify where clause to specify dates of your choice ).

hope this will help.

I'd be wary -

1) Potentially leaves you open to SQL injection.

2) Pretty much all statements are going to be hard-parsed.

thanks for your concern to guide us

i know the seniors are always here to help us, i just give him a solution what i got as he asked for it, and now after your Warnings user (OP) should consider it to use in Production or not.

thanks again.