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:
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: BANGALADESHI hope this article will help you to transpose rows to columns and columns to rows in Oracle.
