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.
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:
Please comment If you have any other method to help me and others too.
Get Free Blogging Tips & Technology updates in your Email !!!
Latest posts by Anil Verma (see all)
- BlueHost Affiliate Program – Earn $65 Per Referral - August 2, 2015
- WP Engine Affiliate Program – Earn $200 Per Referral - August 1, 2015
- How to fill out BlueHost Affiliate Tax form for Non-US Residents - June 20, 2015