OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » select Pivot (sql 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

select Pivot [message #601192]

Mon, 18 November 2013 05:58

mahnazzz
Messages: 98
Registered: July 2011
Location: Iran

Member

hello.
I have a select like this

SELECT *
    FROM   employees
    PIVOT (SUM(salary)
    FOR   MANAGER_ID  in (100)
    )
SQL> /

EMPLOYEE_ID
-----------
        100
        101
        102
        103
        104
        105
        106
        107
        108
        109
        110

EMPLOYEE_ID
-----------
        111
        112
        113
        114
        116
        117
        118
        119
        120
        121
        122

EMPLOYEE_ID
-----------
        123

23 rows selected.

it run successfully.
now according to pivot syntax in oracle sql help I want to use subquery instead of "100".but I get error

  1  SELECT employee_id
  2      FROM   employees
  3      PIVOT (SUM(salary)
  4      FOR   MANAGER_ID  in (select 100 from dual)
  5*     )
SQL> /
    FOR   MANAGER_ID  in (select 100 from dual)
                          *
ERROR at line 4:
ORA-00936: missing expression

the syntax is hear http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#CHDFIIDD
would you please help me Sad