6 ways to convert SQL rows and columns

Source: Internet
Author: User
Tags aliases dname

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.