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_NUMBER | DEPT_ID | DEPT_NAME |
| 1 | 10 | Eng |
| 2 | 20 | Bio |
| 3 | 10 | Eng |
| 4 | 30 | Mat |
SELECT * FROM
(
SELECT id_number, dept_id,dept_name
FROM a_table
)
PIVOT
(
max(dept_name)
FOR dept_id IN (10, 20, 30)
);
| ID_NUMBER | 10 | 20 | 30 |
| 1 | Eng | | |
| 2 | | Bio | |
| 4 | | | Mat |
| 3 | Eng | | |
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.