Transpose Rows to Columns and Columns to Rows in Oracle

This is the most popular question asked in an interview as well as over the internet.

How to transpose rows to columns in SQL?

How to transpose columns to rows in SQL?

How to create PIVOT table in SQL?

How to Create a Matrix in SQL?


We can Transpose Rows to Columns and Columns to Rows in Oracle using below methods and the answers of all the above question is below:

transpose rows to columns


First create a table and insert some records in that table using below mentioned scripts.

CREATE TABLE EMPLOYEE
       (EMP_NO NUMBER(4) NOT NULL,
        EMP_NAME VARCHAR2(20),
        EMP_JOB VARCHAR2(15),
        EMP_MGR NUMBER(4),
        HIRE_DATE DATE,
        EMP_SAL NUMBER(7, 2),
        EMP_COMM NUMBER(7, 2),
        DEPT_NO NUMBER(2));

INSERT INTO EMPLOYEE VALUES
        (7369, 'ADAM SMITH',  'Sr. CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMPLOYEE VALUES
        (7499, 'ALLEN ADAM',  'Sr. SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMPLOYEE VALUES
        (7521, 'ALLEN WARD',   'Sr. SALESMAN',  7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMPLOYEE VALUES
        (7566, 'SMITH JONES',  'Sr. MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMPLOYEE VALUES
        (7654, 'ADAM MARTIN', 'Sr. SALESMAN',  7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMPLOYEE VALUES
        (7698, 'JONES BLAKE',  'Sr. MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMPLOYEE VALUES
        (7782, 'MARTIN CLARK',  'Sr. MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMPLOYEE VALUES
        (7788, 'ADAM SCOTT',  'Sr. ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMPLOYEE VALUES
        (7839, 'JONES KING',   'Sr. PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMPLOYEE VALUES
        (7844, 'MARTIN TURNER', 'Sr. SALESMAN',  7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500, NULL, 30);
INSERT INTO EMPLOYEE VALUES
        (7876, 'ADAMS KING',  'Sr. CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMPLOYEE VALUES
        (7900, 'JAMES LOOTHER',  'Sr. CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMPLOYEE VALUES
        (7902, 'FORD MARTIN',   'Sr. ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMPLOYEE VALUES
        (7934, 'JAMES MILLER', 'Sr. CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

COMMIT;

Query employee table using following scripts to see the row to column conversion.

SELECT  *
  FROM  (SELECT EMP_JOB,
                sum(decode(DEPT_NO,10,EMP_SAL)) DEPT_NO_10,
                sum(decode(DEPT_NO,20,EMP_SAL)) DEPT_NO_20,
                sum(decode(DEPT_NO,30,EMP_SAL)) DEPT_NO_30
           FROM EMPLOYEE
       GROUP BY EMP_JOB)
ORDER BY 1;

EMP_JOB         DEPT_NO_10 DEPT_NO_20 DEPT_NO_30
--------------- ---------- ---------- ----------
Sr. ANALYST                      6000
Sr. CLERK             1300       1900        950
Sr. MANAGER           2450       2975       2850
Sr. PRESIDENT         5000
Sr. SALESMAN                                5600

SQL>

We can also make a total with row to column conversion using below scripts.

SQL> ttitle "Row_to_Column_ Report"

SQL> break on report;

SQL> compute sum of DEPT_NO_10 DEPT_NO_20 DEPT_NO_30 total on report;

SQL> SELECT  *
 	 FROM  (SELECT EMP_JOB,
                sum(decode(DEPT_NO,10,EMP_SAL)) DEPT_NO_10,
                sum(decode(DEPT_NO,20,EMP_SAL)) DEPT_NO_20,
                sum(decode(DEPT_NO,30,EMP_SAL)) DEPT_NO_30,
                sum(EMP_SAL) TOTAL
           FROM EMPLOYEE
       GROUP BY EMP_JOB)
ORDER BY 1;

Sun Nov 10

                               page    1

         Row_to_Column_ Report

EMP_JOB         DEPT_NO_10 DEPT_NO_20 DEPT_NO_30      TOTAL
--------------- ---------- ---------- ---------- ----------
Sr. ANALYST                      6000                  6000
Sr. CLERK             1300       1900        950       4150
Sr. MANAGER           2450       2975       2850       8275
Sr. PRESIDENT         5000                             5000
Sr. SALESMAN                                5600       5600
                ---------- ---------- ---------- ----------
sum                   8750      10875       9400      29025

SQL>

Create a Department table and insert 4 records to see another variant of column to row conversion:

CREATE TABLE DEPARTMENT
       (DEPT_NO NUMBER(2),
        DEPT_NAME VARCHAR2(20),
        DEPT_LOC VARCHAR2(15) );

INSERT INTO DEPARTMENT VALUES (10, 'ACCOUNTING DEPT', 'NEW YORK');
INSERT INTO DEPARTMENT VALUES (20, 'RESEARCH DEPT',   'CHINA');
INSERT INTO DEPARTMENT VALUES (30, 'SALES DEPT',      'BANGALADESH');
INSERT INTO DEPARTMENT VALUES (40, 'OPERATIONS DEPT', 'INDIA');

COMMIT;

Query department table using following scripts to see the column to row conversion.

SELECT DECODE(MOD(a.row#,3)
                  ,1, 'Dept_Number: '  ||DEPT_NO
                  ,2, 'Dept_Name: '    ||DEPT_NAME
                  ,0, 'Dept_Location: '||DEPT_LOC
                  ) AS "Dept_Data"
     FROM DEPARTMENT,
          (SELECT rownum AS row# FROM USER_OBJECTS WHERE rownum < 4) a
    WHERE DEPT_NO = 10;

Dept_Data
---------------------------
Dept_Number: 10
Dept_Name: ACCOUNTING DEPT
Dept_Location: NEW YORK

SELECT DECODE(MOD(a.row#,3)
                  ,1, 'Dept_Number: '  ||DEPT_NO
                  ,2, 'Dept_Name: '    ||DEPT_NAME
                  ,0, 'Dept_Location: '||DEPT_LOC
                  ) AS "Dept_Data"
     FROM DEPARTMENT,
          (SELECT rownum AS row# FROM USER_OBJECTS WHERE rownum < 4) a
    WHERE DEPT_NO = 20;

Dept_Data
--------------------------
Dept_Number: 20
Dept_Name: RESEARCH DEPT
Dept_Location: CHINA

SELECT DECODE(MOD(a.row#,3)
                  ,1, 'Dept_Number: '  ||DEPT_NO
                  ,2, 'Dept_Name: '    ||DEPT_NAME
                  ,0, 'Dept_Location: '||DEPT_LOC
                  ) AS "Dept_Data"
     FROM DEPARTMENT,
          (SELECT rownum AS row# FROM USER_OBJECTS WHERE rownum < 4) a     
WHERE DEPT_NO = 30;    

Dept_Data 
---------------------------- '
Dept_Number: 30 
Dept_Name: SALES DEPT 
Dept_Location: BANGALADESH

Create PIVOT tables in Oracle

I hope this article will help you to transpose rows to columns and columns to rows in Oracle.

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 !!!)