Transpose Rows into Columns

Transpose Rows in Oracle

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.

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)