A general method of column-column conversion

In our work, we often need to use column-column transformation for aggregate statistics and so on. Now let's talk about the simplest and most general method, which adapts to sum () / max () / min () and other functions combined with decode to realize column-column transformation. Take the typical scott.emp table in oracle as an example.

Click (here) to collapse or open

SQL > SELECT * FROM SCOTT.EMP

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20

7839 KING PRESIDENT 1981-11-17 5000.00 10

7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1987-5-23 1100.00 20

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

7934 MILLER CLERK 7782 1982-1-23 1300.00 10

14 rows selected now if you want to count the average salary (or maximum salary, minimum salary, total salary) of employees under each manager by department, you can use row-to-row conversion, which can be achieved by doing the following:

Click (here) to collapse or open

SQL > select deptno

2 avg (decode (mgr,'7566', sal,0)) jones

3 avg (decode (mgr,'7698', sal,0)) BLAKE

4 avg (decode (mgr,'7782', sal,0)) CLARK

5 avg (decode (mgr,'7788', sal,0)) SCOTT

6 avg (decode (mgr,'7839', sal,0)) KING

7 avg (decode (mgr,'7902', sal,0)) FORD

8 from scott.emp

9 group by deptno

10

DEPTNO JONES BLAKE CLARK SCOTT KING FORD

30 0 1091.66666 0 0 475 0

20 1200 00 220 595 160

10 0 0 433.333333 0 816.666666 0

You can see that it is easy to realize the statistics of the data.

Of course, there are several other ways to convert rows and columns, such as using case when or using the wmsys.wm_concat function. But the above method is the easiest to understand.