Transpose Rows into Columns

Step-1: Open Command prompt and issue following command.

START > RUN > CMD

sqlplus /nolog

Step-2: Connect with SYS user and Create another User in Oracle using following command.

conn SYS/SYS_PASSWORD@DB_NAME as SYSDBA

conn sys/sys@mydb as sysdba

CREATE USER TEST IDENTIFIED BY TEST;

GRANT DBA TO TEST;

Now connect with TEST user and create a DEPT table and issue following command.

OR

You can give DBA rights to SCOTT user also with following command.

GRANT DBA TO SCOTT.

Now Login to SCOTT user and run the below script to transpose rows into columns.

select column_id,column_name,  
max( decode(row_id, 1, decode (column_id, 1, to_char (deptno),2 ,dname,3 ,loc))) row1toCol1,  
max( decode(row_id , 2 , decode(column_id, 1 ,to_char(deptno) , 2,dname , 3 ,loc)) ) row2toCol2,  
max( decode(row_id, 3 , decode(column_id,1,to_char(deptno), 2,dname, 3 ,loc))) row3toCol3, 
max( decode ( row_id, 4, decode( column_id,1 ,to_char(deptno),2 ,dname, 3,loc))) row4toCol4    
from (select d.*, rownum row_id from dept d ) d,user_tab_columns utc
where table_name = 'DEPT' group by column_id , column_name  order  by  column_name;

By this way you can transpose rows into columns.

There are many options to create a PIVOT table in Oracle.

You Must Read:

Transpose Rows to Columns and Columns to Rows in Oracle

Please comment If you have any other method to help me and others too.