How to use transform to convert rows and columns;

How to use transform to convert rows and columns;

In Oracle, to convert rows and columns, DECODE and CASE statements are commonly used. For simple row and column conversion, DECODE and CASE statements can still cope with it. In scenarios where the logic is complex and the group aggregation is large, the DECODE and CASE statements are insufficient. The solution can solve all this perfectly.

First, let's take a look at Oracle's explanation:

It can be seen that the histogram is a key technology in the Data Warehouse. It converts rows into columns using the crosstabulation query.

The basic syntax is as follows:

SELECT ....FROM <table-expr>   PIVOT     (      aggregate-function(<column>)      FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)        ) AS <alias>WHERE .....

Next we will elaborate on it through specific cases.

First, construct the data required for the case,

1> Create a view and use the data of the EMP table as the source data.

CREATE VIEW emp_view ASSELECT deptno,job,to_char(hiredate,'yyyy') hiredate, count(*) cnt,sum(sal) sum_salFROM empGROUP BY deptno,job,to_char(hiredate,'yyyy');

Where, deptno is the department number, job is the type of work (that is, the type of work), hiredate is the date of employment, cnt is the specific department, the total number of employees of a specific type of work in a specific year, sum_sal is the sum of the salaries of employees hired in a specific department, type, or year.

2> View data is as follows:

SQL> select * from emp_view;    DEPTNO JOB       HIRE        CNT    SUM_SAL---------- --------- ---- ---------- ----------        20 CLERK     1980          1        800        20 ANALYST   1981          1       3000        20 ANALYST   1987          1       3000        30 CLERK     1981          1        950        30 MANAGER   1981          1       2850        10 MANAGER   1981          1       2450        30 SALESMAN  1981          4       5600        20 MANAGER   1981          1       2975        10 PRESIDENT 1981          1       5000        10 CLERK     1982          1       1300        20 CLERK     1987          1       110011 rows selected.

Application Scenario 1:

Basic sequence Conversion

Example 1:

SELECT * FROM( SELECT deptno,hiredate,cnt  FROM emp_view ) PIVOT (SUM(cnt)   FOR hiredate IN ('1980' AS "1980",'1981' AS "1981",                    '1982' AS "1982",'1987' AS "1987"))ORDER BY deptno;    DEPTNO       1980       1981       1982       1987---------- ---------- ---------- ---------- ----------        10                     2          1        20          1          2                     2        30                     63 rows selected.

Example 2:

SELECT * FROM( SELECT deptno,job,cnt  FROM emp_view ) PIVOT (SUM(cnt)   FOR job IN ('CLERK','ANALYST','MANAGER','SALESMAN','PRESIDENT'))ORDER BY deptno;    DEPTNO    'CLERK'  'ANALYST'  'MANAGER' 'SALESMAN' 'PRESIDENT'---------- ---------- ---------- ---------- ---------- -----------        10          1                     1                      1        20          2          2          1        30          1                     1          43 rows selected.

The two examples use different columns for statistics. The former is hiredate, and the latter is job.

In addition, the former uses an alias, and the latter does not use an alias. The display effect of the two is also different.

Application Scenario 2:

Convert multiple columns

SELECT * FROM( SELECT deptno,job,hiredate,cnt  FROM emp_view ) PIVOT (SUM(cnt)            FOR (job,hiredate) IN              (('CLERK','1980') AS clerk_1980,               ('CLERK','1981') AS clerk_1981,               ('ANALYST','1987') AS analyst_1987,               ('MANAGER','1981') AS manager_1981              )           )ORDER by deptno;    DEPTNO CLERK_1980 CLERK_1981 ANALYST_1987 MANAGER_1981---------- ---------- ---------- ------------ ------------        10                                               1        20          1                       1            1        30                     1                         13 rows selected.

Due to space limitations, the FOR (job, hiredate) IN statement does not list more combinations. Only four groups are listed. Of course, we can list more combinations according to actual scenarios.

In this example, we can see that the histogram conversion of two columns can present the statistical results from three dimensions.

Application Scenario 3:

Use aggregate to implement multiple Aggregation

SELECT * FROM( SELECT deptno,hiredate,cnt,sum_sal  FROM emp_view ) PIVOT ( SUM(cnt) AS cnt,           SUM(sum_sal) AS sum_sal           FOR hiredate IN ('1980','1981','1982','1987'))ORDER BY deptno;    DEPTNO '1980'_CNT '1980'_SUM_SAL '1981'_CNT '1981'_SUM_SAL '1982'_CNT '1982'_SUM_SAL '1987'_CNT '1987'_SUM_SAL---------- ---------- -------------- ---------- -------------- ---------- -------------- ---------- --------------        10                                    2           7450          1           1300        20          1            800          2           5975                                    2           4100        30                                    6           94003 rows selected.

'000000' _ CNT refers to the total number of employees hired in 1981, and '000000' _ SUM_SAL refers to the salary paid by employees hired in 1981.

In this example, two employees are recruited in department 10 on April 9, 1981, with a total salary of 7450 RMB. Department 20 has hired two employees, the total salary is 5975 yuan, 6 employees are recruited in department 30, and the total salary is 9400 yuan, and so on.

Since rows are converted to columns by rows, the unaggregate operation also converts the aggregated columns to rows.

 

Unregister

The result of scenario 3 is used as the source data for operations.

CREATE TABLE T1 ASSELECT * FROM( SELECT deptno,hiredate,cnt,sum_sal  FROM emp_view ) PIVOT ( SUM(cnt) AS cnt,           SUM(sum_sal) AS sum_sal           FOR hiredate IN ('1980' AS "1980",'1981' AS "1981",                            '1982' AS "1982",'1987' AS "1987"))ORDER BY deptno

The result of table T1 is:

SQL> select * from t1;    DEPTNO   1980_CNT 1980_SUM_SAL   1981_CNT 1981_SUM_SAL   1982_CNT 1982_SUM_SAL   1987_CNT 1987_SUM_SAL---------- ---------- ------------ ---------- ------------ ---------- ------------ ---------- ------------        10                                  2         7450          1         1300        20          1          800          2         5975                                  2         4100        30                                  6         94003 rows selected.

First, perform one-dimensional unregister

SELECT deptno,DECODE(hiredate,'1980_CNT','1980','1981_CNT','1981','1982_CNT','1982','1987_CNT','1987') AS hiredate,cntFROM T1UNPIVOT INCLUDE NULLS( cnt  FOR hiredate IN ("1980_CNT","1981_CNT","1982_CNT","1987_CNT"));    DEPTNO HIRE        CNT---------- ---- ----------        10 1980        10 1981          2        10 1982          1        10 1987        20 1980          1        20 1981          2        20 1982        20 1987          2        30 1980        30 1981          6        30 1982        30 198712 rows selected.

The output result shows the number of employees in different departments in different years,

Note: In the preceding SQL statement, INCLUDE NULLS is added after unblocking. You can also specify EXCLUDE NULLS to EXCLUDE the value of cnt as null. If not specified, EXCLUDE NULLS is used by default.

If you do not specify include nulls after uncommitted, the input result is:

    DEPTNO HIRE        CNT---------- ---- ----------        10 1981          2        10 1982          1        20 1980          1        20 1981          2        20 1987          2        30 1981          66 rows selected.

Next, we will perform a two-dimensional unregister

SELECT deptno,hiredate,cnt,sum_salFROM T1UNPIVOT( (cnt,sum_sal)  FOR hiredate IN (("1980_CNT","1980_SUM_SAL") AS 1980,                   ("1981_CNT","1981_SUM_SAL") AS 1981,                   ("1982_CNT","1982_SUM_SAL") AS 1982,                   ("1987_CNT","1987_SUM_SAL") AS 1987));    DEPTNO   HIREDATE        CNT    SUM_SAL---------- ---------- ---------- ----------        10       1981          2       7450        10       1982          1       1300        20       1980          1        800        20       1981          2       5975        20       1987          2       4100        30       1981          6       94006 rows selected.

The input result is the result of converting table T1 to a row.

Reference:

SQL for Analysis and Reporting