OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Pivot query (19c)

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

Pivot query [message #686638]

Mon, 07 November 2022 22:35

glmjoy
Messages: 187
Registered: September 2011
Location: KR

Senior Member

My query is as below which is giving result perfectly. But I want to make dynamic in Pivot query for e.g can I use select deptno in pivot
Sum(sal) for DeptNo in (10,20,30,40) I am doing this which is saying
ERROR at line 6:
ORA-00936: missing expression

select * from (
select nvl(deptno, -1) deptno , job , sum(sal) SAL from scott.emp
group by cube (deptno , job )
)
pivot (
Sum(sal) for DeptNo in (10,20,30,40)
)
order by job

Result

JOB 10 20 30 40
--------- ---------- ---------- ---------- ----------
ANALYST 6000
CLERK 1300 1100 950
MANAGER 2450 3775 2850
PRESIDENT 5000
SALESMAN 1000 5600
9750 10875 9400
---------------------------

But when I use (Select deptno from scott.emp) after

-----------------------------
select * from (
select nvl(deptno, -1) deptno , job , sum(sal) SAL from scott.emp
group by cube (deptno , job )
)
pivot (
Sum(sal) for DeptNo in (Select deptno from scott.emp)
)
order by job

Its give me error

ERROR at line 6:
ORA-00936: missing expression

may be I am doing something wrong

Report message to a moderator

Re: Pivot query [message #686639 is a reply to message #686638]

Tue, 08 November 2022 00:04

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 28 October 2021 07:28

Michel Cadot wrote on Sat, 04 September 2021 16:33
Michel Cadot wrote on Wed, 28 July 2021 10:38

Please format your post as explained in How to use [code] tags and make your code easier to read, and align the column in result.

John Watson wrote on Sat, 04 September 2021 09:50
You have been asked, more than once, to follow the Forum rules: Format your posts correctly using [code] tags for code, and use type casting functions such as TO_DATE when working with dates and strings.

It us extremely rude of you to ignore these requests.

Your answer:

glmjoy wrote on Sat, 04 September 2021 10:05
sorry for that
You are so sorry that you ignore it in your next topic.

...