6 ways to convert SQL rows and columns

In the development of the report, many times will encounter row and column conversion operations, the developer for the SQL level row and column conversion operation has been not very understanding, today just time to make some simple summary. Here are 3 ways to implement SQL row and column conversions, including General SQL Solutions and Oracle Support solutions. First, test data

The test table still uses the Dept and EMP tables in the Oracle Classic Scott mode, with the following structure:

DEPT:

CREATE TABLE DEPT ( DEPTNO Number (2) is not NULL, Dname VARCHAR2 (14), LOC VARCHAR2 (13) ) --Create/recreate primary, unique and foreign KEY constraints ALTER TABLE DEPT Add constraint Pk_dept primary key (DEPTNO) Using Index;

Sql> select * FROM dept; DEPTNO dname LOC ------ -------------- ------------- Ten ACCOUNTING NEW YORK DALLAS SALES CHICAGO OPERATIONS BOSTON

Emp:

CREATE table EMP ( EMPNO number (4) not NULL, ename VARCHAR2 (10 ), JOB VARCHAR2 (9), MGR number (4), HireDate DATE, SAL number (7,2), COMM number (7,2), DEPTNO Number (2), SEX VARCHAR2 (2) Default ' male ' ) --create/recreate primary, unique and FOREIGN KEY constraints ALTER TABLE EMP Add constraint pk_emp primary key (EMPNO) Using index; ALTER TABLE EMP Add constraint Fk_deptno foreign key (DEPTNO) References DEPT (DEPTNO);

sql> select * from emp; EMPNO ename JOB MGR hiredate SAL COMM DEPTNO SEX ------------------------------------------------------------------- 7369 SMITH Clerk 7902 1980/12/17 800.00 20 male 7499 ALLEN salesman 7698 1981/2/20 1600.00 300.00 30 Female 7521 WARD salesman 7698 1981/2/22 1250.00 500.00 30 Female 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 female 7654 MARTIN salesman 7698 1981/9/28 1250.00 1400.00 30 female 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 female 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 female 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 male 7839 KING President 1981/11/17 5000.00 10 women 7844 TURNER salesman 7698 1981/9/8 1500.00 0.00 30 Female 7876 ADAMS Clerk 7788 1987/5/23 1100.00 20 male 7900 JAMES Clerk 7698 1981/12/3 950.00 30 women 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 female 7934 MILLER Clerk 7782 1982/1/23 1300.00 10 male Rows selected

Second, row and column conversion method

Requirements: Check the total salary for different positions in each department.

1, the most traditional SQL solution is as follows:

Sql> Select D.dname, E.job, sum (SAL) as Sum_sal 2 from EMP E, Dept D 3 Where E.deptno = D.deptno 4 GROUP by D.dname, E.job; Dname JOB Sum_sal -------------- --------- ---------- SALES MANAGER 2850 SALES Clerk 950 ACCOUNTING MANAGER 2450 ACCOUNTING President 5000 ACCOUNTING Clerk 1300 The MANAGER 2975 SALES salesman 5600 ANALYST 6000 Clerk 1900 9 Rows selected

The above method does get the sum of the salary of different positions in different departments, but we see that each department has multiple lines of information, which is not the result we want, we would like to show each department as 1 rows.

2, the use of sub-query to achieve row and column conversion:

Sql> Select E.deptno, 2 (select sum (SAL) from emp where Emp.deptno = E.deptno and job= ' ANALYST ') as Analyst_job, 3 (select sum (SAL) from EMP where Emp.deptno=e.deptno and job= ' clerk ') as Clerk_job, 4 (select sum (SAL) from EMP where Emp.deptno=e.deptno and job= ' MANAGER ') as Manager_job, 5 (select sum (SAL) from EMP where Emp.deptno=e.deptno and job= ' President ') as Persident_job, 6 (select sum (SAL) from EMP where Emp.deptno=e.deptno and job= ' salesman ') as Salesman_job 7 from EMP E 8 GROUP BY E.deptno; DEPTNO analyst_job clerk_job manager_job persident_job salesman_job ------ ----------- ---------- ----------- ------------- ------------ 30 950 2850 5600 20 6000 1900 2975 10 1300 2450 5000

3. Try to change the SQL to achieve the following form:

Sql> Select Dname, 2 SUM (manager_job) as Sum_manager_sal, 3 SUM (President_job) as Sum_president_sal, 4 SUM (Clerk_job) as Sum_clerk_sal, 5 SUM (Analyst_job) as Sum_analyst_sal, 6 SUM (salesman_job) as Sum_salesman_sal 7 from (select D.dname, 8 (select sum (SAL) 9 from EMP Ten WHERE emp.empno = E.empno Emp.job = ' MANAGER ') as Manager_job, (select sum (SAL) From EMP where emp.empno = E.empno Emp.job = ' president ') as President_job, (select sum (SAL) From EMP where emp.empno = E.empno Emp.job = ' clerk ') as Clerk_job, (select sum (SAL) From EMP where emp.empno = E.empno Emp.job = ' ANALYST ') as Analyst_job, (select sum (SAL) From EMP -Where emp.empno = E.empno Emp.job = ' salesman ') as Salesman_job From EMP E, Dept D where E.deptno = D.deptno) Temp GROUP BY Temp.dname Order BY Temp.dname; Dname sum_manager_sal sum_president_sal sum_clerk_sal sum_analyst_sal sum_salesman_sal -------------- --------------- ----------------- ------------- --------------- ---------------- ACCOUNTING 2450 5000 1300 2975 1900 6000 SALES 2850 950 5600

We can see from the query results that we have implemented the row-and-column transformation, and this result is consistent with our daily reading habits. But the code is too complex, and the execution plan returns significantly more often. The code here basically conforms to any kind of SQL-supported database basic syntax, belongs to the general pattern, then there is no simple way? The answer is YES!

4. Line-and-column conversions based on the Oracle decode () function:

Sql> Select D.dname, 2 sum (decode (Job, ' MANAGER ', sal, 0)) as Sum_manager_sal, 3 sum (decode (Job, ' president ', Sal, 0)) as Sum_president_sal, 4 sum (decode (Job, ' clerk ', Sal, 0)) as Sum_clerk_sal, 5 sum (decode (Job, ' ANALYST ', sal, 0)) as Sum_analyst_sal, 6 sum (decode (Job, ' salesman ', Sal, 0)) as Sum_salesman_sal 7 from EMP E, Dept D 8 Where E.deptno = D.deptno 9 GROUP by D.dname; Dname sum_manager_sal sum_president_sal sum_clerk_sal sum_analyst_sal sum_salesman_sal -------------- --------------- ----------------- ------------- --------------- ---------------- ACCOUNTING 2450 5000 1300 0 0 2975 0 1900 6000 0 SALES 2850 0 950 0 5600

Based on the results above, we can see that the decode () function of the Oracle database achieves the purpose of row-and-column loading, and is obviously more efficient for the previous row and column conversion operations.

5. The universal row and column conversion can be achieved through case when ... then ... else ... end:

Sql> Select D.dname, 2 sum (case 3 when e.job = "ANALYST" Then 4 sal 5 Else 6 0 7 end) as Analyst_job, 8 SUM (case 9 when e.job = ' Clerk ' Then Ten Sal One else 12 0 (end) as Clerk_job, SUM (case When e.job = "MANAGER" Then Sal + Else 18 0 (end) as Manager_job, SUM (case E.job = ' President ' Then Sal All else 24 0 (end) as President_job, SUM (case When e.job = ' salesman ' Then Sal Else 30 0 (end) as Salesman_job From EMP E, Dept D where E.deptno = D.deptno Group BY D.dname; Dname analyst_job clerk_job manager_job president_job salesman_job -------------- ----------- ---------- ----------- ------------- ------------ ACCOUNTING 0 1300 2450 5000 0 6000 1900 2975 0 0 SALES 0 950 2850 0 5600

6. The pivot and UNPIVOT functions are introduced in Oracle 11g, specifically for row and column conversions, and the pivot function syntax is as follows:

Select *| Column [alias],... From sub-query Pivot statistic function ( column) s for conversion column name in ( content 1 [[as] aliases], Content 2 [[as] aliases], ... ... content n [[as] aliases] ) ) [Where condition (s)] [GROUP by Group Field 1, Group field 2,... ...] [Having filter condition (s)] [Order by sort field Asc|desc]

Use the pivot function to implement row and column conversions:

Sql> SELECT * 2 from (select D.dname, E.job, e.sal 3 from EMP E, Dept D 4 where E.deptno = d.deptno) pivot (sum (SAL) for job in (' ANALYST ' as 5 Analyst_job, 6 ' clerk ' as 7 Clerk_job, 8 ' MANAGER ' as 9 Manager_job, ' President ' as President_job, ' Salesman ' as Salesman_job)) The order by Dname; Dname analyst_job clerk_job manager_job president_job salesman_job -------------- ----------- ---------- ----------- ------------- ------------ ACCOUNTING 1300 2450 5000 6000 1900 2975 SALES 950 2850 5600

The above SQL uses the PIVOT function to implement row and column conversion operations. At this point, we use 3 methods for row and column conversion operations.

The following is an example of a more complex SQL that the pivot function implements:

6.1. Inquire about the total salary of each department, the total salary of each department and the maximum wage and the minimum wage for each department:

sql> SELECT * 2 from (select Deptno, 3 job, 4 sal, 5 sum (SAL) over ( Partition by Deptno) as Sum_sal, 6 max (SAL) over (partition by Deptno) as Max_sal, 7 min (sal) over (partition by DEPTNO) as Min_sal 8 from EMP) pivot (SUM (SAL) for job in (' ANALYST ' as Analyst_job, 9 ' clerk ' as clerk_job, ' MANAGER ' as manager_job, One president ' as President_job, [salesman ' as Salesman_job ') 13 Order by Deptno; DEPTNO sum_sal max_sal min_sal analyst_job clerk_job manager_job president_job salesman_job --------------------------------------------------------------------------------------------- 8750 1300 1300 2450 10875 6000 1900 2975 9400 2850 950 950 2850 5600

6.2. Check the total salary of different positions in each department and the maximum salary of each department in different positions:

Sql> SELECT * 2 from (select Deptno, Job, Sal from EMP) 3 Pivot (SUM (SAL) as Sum_sal, Max (SAL) as Sum_max 4 for-Job in (' ANALYST ' as Ana_job, 5 ' clerk ' as Cle_job, 6 ' MANAGER ' as man_job/*, 7 ' president ' as Pre_job, 8 ' salesman ' as sal_job*/) 9) Ten order by Deptno; DEPTNO ana_job_sum_sal ana_job_sum_max cle_job_sum_sal cle_job_sum_max man_job_sum_sal MAN_JOB_SUM_MAX ------ --------------- --------------- --------------- --------------- --------------- --------------- 10 1300 1300) 2450 2450 20 6000 3000 1900 1100 2975 2975 30 950 950) 2850 2850

6.3. Statistics of the total wage and the maximum wage of different sexes in each department:

sql> SELECT * 2 from (select Deptno, Job, sex, sal from EMP) p>3 pivot (SUM (SAL) as Sum_sal, Max (SAL) as Sum_max 4 for (Job, Sex) in (' MANAGER ', ' Male ') as Manager_job_man, 5 ( ' MANAGER ', ' female ') as manager_job_woman/*, 6 (' clerk ', ' Male ') as Clerk_job_man, 7 (' clerk ', ' female ') as Clerk_job_woma N, 8 (' analyst ', ' Male ') as Analyst_job_man, 9 (' analyst ', ' female ') as Analyst_job_woman, Ten (' President ', ' Male As President_job_man, One (' president ', ' female ') as President_job_woman, (' salesman ', ' Male ') as Salesman_job_ Man, (' salesman ', ' female ') as salesman_job_woman*/) , order by Deptno; DEPTNO manager_job_man_sum_sal Manager_job_man_sum_max manager_job_woman_sum_sal Manager_job_woman_sum_max ------------------------------------------------------------------------------------------------------ 2450 2450 2975 2975 2850 2850

6.4. If you add an XML display in pivot, you can use any to set all the data you want to manipulate, currently any can only be used in the XML display:

Sql> SELECT * 2 from (select Deptno, Job, sal from EMP) pivot XML (SUM (SAL) for job in (any)) 3 Order by Deptno;

6.5, Pivot function is to convert rows to columns, the same UNPIVOT function for column change operation, the operation syntax is as follows:

Select *| Column [alias],... From sub-query Unpivot [include Nulls|exclude nulls ( Default)] ( statistic function ( column) s for conversion column name in ( content 1 [[as] aliases], Content 2 [[as] aliases], ... ... content n [[as] aliases] ) ) [Where condition (s)] [GROUP by Group Field 1, Group field 2,... ...] [Having filter condition (s)] [Order by sort field Asc|desc]

If you turn the SQL result of the row in step 2 back to the line:

Sql> with Temp as 2 (SELECT * 3 from (select D.dname, E.job, e.sal 4 from EMP E, Dept D 5 where E.deptno = d.deptno) pivot (sum (SAL) for job in (' ANALYST ' as 6 Analyst_job, 7 ' clerk ' as 8 Clerk_job, 9 ' MANAGER ' as Ten Manager_job, One ' president ' as President_job, ' Salesman ' as Salesman_job)) ORDER by Dname) SELECT * From temp Unpivot (sum_sal for job in (Analyst_job as ' ANALYST ', Clerk_job as ' clerk ', Manager_job as ' MANAGER ', President_job as ' president ', (Salesman_job as ' salesman ')); Dname JOB Sum_sal -------------- --------- ---------- ACCOUNTING Clerk 1300 ACCOUNTING MANAGER 2450 ACCOUNTING President 5000 ANALYST 6000 Clerk 1900 The MANAGER 2975 SALES Clerk 950 SALES MANAGER 2850 SALES salesman 5600 9 Rows selected

As you can see from the above results, the results here have converted columns to rows, but not nulls worthwhile, if the industry that wants to include nulls values shows the life include nulls keyword that needs to be displayed:

Sql> with Temp as 2 (SELECT * 3 from (select D.dname, E.job, e.sal 4 from EMP E, Dept D 5 where E.deptno = d.deptno) pivot (sum (SAL) for job in (' ANALYST ' as 6 Analyst_job, 7 ' clerk ' as 8 Clerk_job, 9 ' MANAGER ' as Ten Manager_job, One ' president ' as President_job, ' Salesman ' as Salesman_job)) ORDER by Dname) SELECT * From temp unpivot include nulls (sum_sal for job in (Analyst_job as ' ANALYST ', Clerk_job as ' clerk ', Manager_job as "MANAGER", President_job as ' President ', Salesman_job as ' salesman ')); Dname JOB Sum_sal -------------- --------- ---------- ACCOUNTING ANALYST ACCOUNTING Clerk 1300 ACCOUNTING MANAGER 2450 ACCOUNTING President 5000 ACCOUNTING salesman ANALYST 6000 Clerk 1900 The MANAGER 2975 President Salesman SALES ANALYST SALES Clerk 950 SALES MANAGER 2850 SALES President SALES salesman 5600 Rows selected

6 ways to convert SQL rows and columns