May 19, 2010

Converting rows to columns and vice versa:


          Converting rows to columns:
Sometimes we need some reports that somehow require converting rows to columns. Here we are going to put a simple example on how to do so.
We all know emp table in scott schema that contains deptno column, and we know that deptno column has three distinct values: 10, 20 and 30 –no value 40, but any way you can include it to make sure-.

EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
7839
KING
PRESIDENT
-
17-Nov-81
5000
-
10
7698
BLAKE
MANAGER
7839
1-May-81
2850
-
30
7782
CLARK
MANAGER
7839
9-Jun-81
2450
-
10
7566
JONES
MANAGER
7839
2-Apr-81
2975
-
20
7788
SCOTT
ANALYST
7566
9-Dec-82
3000
-
20
7902
FORD
ANALYST
7566
3-Dec-81
3000
-
20
7369
SMITH
CLERK
7902
17-Dec-80
800
-
20
7499
ALLEN
SALESMAN
7698
20-Feb-81
1600
300
30
7521
WARD
SALESMAN
7698
22-Feb-81
1250
500
30
7654
MARTIN
SALESMAN
7698
28-Sep-81
1250
1400
30
7844
TURNER
SALESMAN
7698
8-Sep-81
1500
0
30
7876
ADAMS
CLERK
7788
12-Jan-83
1100
-
20
7900
JAMES
CLERK
7698
3-Dec-81
950
-
30
7934
MILLER
CLERK
7782
23-Jan-82
1300
-
10

 Let’s say we need a report that shows each department –as columns- and its employees under it:
SELECT empno, 


       DECODE(deptno, 10, ename) "10",         DECODE(deptno, 20, ename) "20",         DECODE(deptno, 30, ename) "30"  FROM   emp  ORDER  BY 2, 3, 4;


EMPNO
10
20
30
7782
CLARK
-
-
7839
KING
-
-
7934
MILLER
-
-
7876
-
ADAMS
-
7902
-
FORD
-
7566
-
JONES
-
7788
-
SCOTT
-
7369
-
SMITH
-
7499
-
-
ALLEN
7698
-
-
BLAKE
7900
-
-
JAMES
7654
-
-
MARTIN
7844
-
-
TURNER
7521
-
-
WARD

If we want to employees count for each department:
SELECT COUNT(DECODE(deptno, 10, ename)) "10", 


       COUNT(DECODE(deptno, 20, ename)) "20",         COUNT(DECODE(deptno, 30, ename)) "30"  FROM   emp  GROUP  BY deptno ;


10
20
30
0
0
6
0
5
0
3
0
0


And so on we can convert rows to columns as long as the values we want to make them as columns are not so many.
Note that the key is DECODE function.

-          Converting columns to rows:
Create test table:
CREATE TABLE convert_c2r 


  (       id     NUMBER,       value1 NUMBER,       value2 NUMBER,       value3 NUMBER,       value4 NUMBER    ); 

Insert random data:
INSERT INTO convert_c2r 


VALUES      (1, 1, 2, 3, 4);  INSERT INTO convert_c2r  VALUES      (2, 2, 4, 6, 8);  INSERT INTO convert_c2r  VALUES      (3, 3, 6, 9, 12); INSERT INTO convert_c2r  VALUES      (4, 4, 8, 12, 16);

View table data to ckeck:
SELECT * 


FROM   convert_c2r; 


ID
VALUE1
VALUE2
VALUE3
VALUE4
1
1
2
3
4
2
2
4
6
8
3
3
6
9
12
4
4
8
12
16

and to convert columns to rows:
SELECT id, 


       ser,         DECODE(ser, '1', value1,                     '2', value2,                     '3', value3,                     '4', value4) d  FROM   convert_c2r,         (SELECT '1' ser          FROM   dual          UNION          SELECT '2' ser          FROM   dual          UNION          SELECT '3' ser          FROM   dual          UNION          SELECT '4' ser          FROM   dual) ; 


ID
SER
D
1
1
1
2
1
2
3
1
3
4
1
4
1
2
2
2
2
4
3
2
6
4
2
8
1
3
3
2
3
6
3
3
9
4
3
12
1
4
4
2
4
8
3
4
12
4
4
16

Saad Nayef,