CODEWITHSUNDEEP
sqloracle-databasepivot
Raghavendra M
Raghavendra M

Reputation: 197

PIVOT function in Oracle 11G giving invalid identifier

I am trying to work on PIVOT function when I write a simple query to get department number and number of employees in that department, I am getting error.

Here is my query

select deptno ,emp_count from ( select deptno from EMPLOYEE ) PIVOT ( COUNT(name) for deptno in('10','20','30') );

I am getting following error

ORA-00904: "NAME": invalid identifier 00904. 00000 - "%s: invalid identifier"

this is my table

enter image description here

Can any one help me in writing and understanding of PIVOT function in SQL query.

Upvotes: 0

Views: 6429

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

Syntax of your query is not right. The proper way to write it would be as shown below. Note that I have used aliases of the form emp_count_? ; you can modify it as per you need.

SELECT
   * 
FROM
   (
      SELECT
         deptno,
         name 
      FROM
         employee 
   )
   PIVOT (COUNT(name) FOR deptno IN
   (
      '10' AS emp_count_10,
      '20' AS emp_count_20,
      '30' AS emp_count_30
   )
);

Upvotes: 2

Related Questions