Pivot and unpivot functions

Today's editors are all examples of fixed row switching (column switching)!

One: unpivot column Wrap function

Give an example to demonstrate:

Create a table tmp_test with the data shown in the figure

Code display:

Select code,name,cource,grade from tmp_test

Unpivot (

Grade for source in (chinese,math,english)

);

Display of data results:

Two: pivot row-to-column function

Give an example to demonstrate:

Create a table tmp_test2 with the data shown in the figure

Code display:

Select *

From (select username,subject,source from tmp_test2)

Pivot (sum (source))

For subject in ('Chinese', 'Mathematics', 'English'))

Display of data results:

In fact, the sql can also be implemented with the decode function:

Select username

Sum (decode (subject,'', source,0)) language

Sum (decode (subject,' Mathematics', source,0)) Mathematics

Sum (decode (subject,' English', source,0)) English

From tmp_test2

Group by username

Summary:

Pivot function: row to column function:

Syntax: pivot (any aggregate function for needs the column name in where the value of the special column is located (the value to be converted to the column name))

Unpivot function: column transfer function:

Syntax: unpivot (the column name of the new value added column for the column name of the column in which the row is changed to in (the column name that needs to be converted to the row))

How it works: connect the pivot function or unpivot function to the end of the query result set. It is equivalent to processing the result set.

Note: other people say that in can be followed by a sub-query statement, which I am not sure, but I tried it myself in this example, it is not allowed. ORA-00936: missing expression

On this point, interested friends can try again in private!

Let's call it a day. As for dynamic row rotation, we'll discuss it next time!