How to transpose rows and columns in oracle

Oracle row and column transposition method: 1. Use the pivot() function to convert rows to columns. The syntax is "pivot (aggregation function for column name in (type))"; 2. Use the unpivot() function to convert columns to rows. , the syntax is "unpivot(column name for column name in (type))".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

Oracle How to Transpose Rows and Rows

I have been exposed to row and column transposition in general SQL before. To transfer rows and columns, use the aggregate function sum | max and then combine it with the case when then else end statement, and Column to row is implemented using union or union all. This is actually a bit troublesome, and in Oracle data, such a function is specially provided to realize the row and column transposition function.

You can also use general SQL to implement row and column transposition, but Oracle provides special functions to handle row and column transposition, that is, using the pivot function and unpivot function, which are introduced below.

1. Pivot row to column

Oracle database provides the pivot function to realize the row to column function. It can also achieve the above row to column effect. SQL can also Write like this:

SELECT NAME AS
	姓名,
	"'语文'",
	"'数学'",
	"'英语'" FROM
	stu_score pivot ( max( score ) FOR SUBJECT IN ( '语文', '数学', '英语' ) );

The query results are as follows:

2. Unpivot column conversion

Oracle provides the unpivot function to implement The column to row function can achieve the same effect. SQL can also be written like this:

SELECT NAME AS
	姓名,
	月份,工资 
FROM
	e_sal unpivot ( 工资 FOR 月份 IN ( M_1, M_2, M_3, M_4 ) );

The query results are as follows:

Recommended tutorial: "Oracle Video Tutorial》

The above is the detailed content of How to transpose rows and columns in oracle. For more information, please follow other related articles on the PHP Chinese website!