When trying to create a pivot, I get a ORA-00904 invalid identifier error on the following query.
Table Definition
create table table1(id int, name varchar2(20), col1 int);
insert into table1 values(1, 'Alex', 99);
insert into table1 values(2, 'Alex', 98);
insert into table1 values(3, 'James', 97);
insert into table1 values(4, 'Eric', 99);
insert into table1 values(5, 'Stan', 99);
Error Query
select name, col1
from table1
pivot (count(name) for col1 in (99, 98, 97))
;
However, the following queries will work
Working Query 1
select *
from table1
pivot (count(name) for col1 in (99, 98, 97)) p
;
Working Query 2
with cte as (
select name, col1
from table1
)
select *
from cte
pivot (count(name) for col1 in (99, 98, 97))
;
I prefer the output of Working Query 2 as I get the counts without all the other data included
99 | 98 | 97
-------|----|-------
3 | 1 | 1
Why does the error occur when attempting to pivot when specifying the columns directly from the table?
After the pivot, name is no longer in the result set. It has been replaced by the count and columns with names like "99".
That is why one often uses select * with pivot. Most of the columns have already been listed in the in clause.