Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

PIVOT SQL

a47c623f-b4ee-43f6-94ef-f926b9e097ddJun 19 2017 — edited Jun 19 2017

Hello Team,

I was trying to query the table for dynamic columns from rows.

I am good with the following example below,

create table a_table(id_number number, dept_id number, dept_name varchar2(40));

insert into a_table(id_number,dept_id,dept_name) values(1,10,'Eng');

insert into a_table(id_number,dept_id,dept_name) values(2,20,'Bio');

insert into a_table(id_number,dept_id,dept_name) values(3,10,'Eng');

insert into a_table(id_number,dept_id,dept_name) values(4,30,'Mat');

select * from a_table;

ID_NUMBERDEPT_IDDEPT_NAME
110Eng
220Bio
310Eng
430Mat

SELECT * FROM

(

  SELECT id_number, dept_id,dept_name

  FROM a_table

)

PIVOT

(

  max(dept_name)

  FOR dept_id IN (10, 20, 30)

);

ID_NUMBER102030
1Eng
2Bio
4Mat
3Eng

Question: Pivot works fine if I send static data into FOR statement of the query.

But I need to pass the dynamic data so that the column names should be dynamic.

For example if I have 100 departments I want to extract the data with heading as 100 departments as column names.

Any help for this question would be greatly appreciated. Thank You.

Regards,

Anil Kumar.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 17 2021
Added on Jun 19 2017
3 comments
813 views