CODEWITHSUNDEEP
sqloracle-databasepivot
Sunitha
Sunitha

Reputation: 135

pivot query in oracle

I have one requirement to pivot the data. I have tried below query by using pivot:

select * from 

(
  select g.geography_code,
       g.description,
       a.external_id_1 as ext
from geography_alignment_timeline gal
join geography g on g.geography_id=gal.geography_id
join alignment a on gal.alignment_id=a.alignment_id
join team t on a.team_id=t.team_id
where g.tenant_id=500104
and   g.region_id=7888500001071037
and   t.team_id=70000012130101
)
pivot
(
  max(ext) for ext in (select external_id_1 from alignment where tenant_id=500104)
);

and am getting the error:

ORA-00936: missing expression

 Tables and columns: 
     Geography : Geography_id,Geography_code,Description,External_id_1
     Geography_alignment_timeline : Geography_id,Alignment_id,External_id_1
     Alignment : Alignment_id,team_id,Alignment_name,External_id_1
     team : Team_id,name,Description,External_id_1

I want to display the data like below..

enter image description here

each team having separte alignment_ids. i want to display the data like above.

Can you please help me what is the issue?

Upvotes: 0

Views: 602

Answers (2)

Smart003
Smart003

Reputation: 1119

read the this document which was related to pivot clause.

as per my understanding

A subquery is used only in conjunction with the XML keyword.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191235

You cannot pivot dynamically; the number of values (which corresponds to the number of columns in the final result set) need to be known at parse time. It is not valid syntax to use a subquery in the pivot's IN clause. You need to hard-code the values to expect to see.

It is possible to have a dynamic XML pivot but that probably won't help you here; you'd need to unpack the XML somehow. You may also be able to use dynamic SQL to generate the query but a ref cursor result might not be appropriate for you.

Upvotes: 1

Related Questions