1

I have a table commission_new as output of commission_new table:

enter image description here

when I use the SQL:

 select * from  
    (   select employee_no,years ,subtotal from
        commission_new
    )
    pivot
    (  sum(subtotal) for years in (1999,2001,2000,2003)
        --(select distinct nvl(years,0) from commission_new)   
    )
order by employee_no;

I get the result

enter image description here

but if I use

(select distinct nvl(years,0) from commission_test )

instead of 

(1999,2001,2000,2003)

I get the error:

enter image description here

Error at Line: 7 Column: 6 ORA-00936: missing expression

even though the SQL select distinct nvl(years,0) from commission_test gives the same years as result. What may be the reason of this?

dllhell
  • 1,987
  • 3
  • 34
  • 51
Pranav Adhikari
  • 23
  • 4
  • 4
    That's not an allowed syntax : You may want dynamic Pivot instead, See this https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql – Kaushik Nayak Jul 13 '18 at 06:43
  • There was also quite interesting solution, described here: https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/ – artbro Jul 13 '18 at 07:13

0 Answers0