Convert Oracle columns to rows

First, we will introduce how to convert rows into columns,

Oracle row-to-column conversion is common. Common examples on the Internet are as follows:

Grades table:

Student subject grade

Student1 language 80

Student1 mathematics 70

Student1 English 60

Student2 language 90

Student2 mathematics 80

Student2 English 10

Convert

Chinese, mathematics, and English

Student1 80 70 60

Student2 90 80 100

The execution statement is as follows:

1. SelectStudent,
3. Sum(Decode (subject,'China', Grade,Null))"Language",
5. Sum(Decode (subject,'Mat', Grade,Null))"Mathematics",
7. Sum(Decode (subject,'English', Grade,Null))"English"
9. FromGrades
11. Group ByStudentOrder ByStudent;

1. SelectStudent,Sum(Decode (subject,'China', Grade,Null))"Language",Sum(Decode (subject,'Mat', Grade,Null))"Mathematics",Sum(Decode (subject,'English', Grade,Null))"English"FromGradesgroupByStudentOrder ByStudent;

The following describes how to convert a column to a row:

Assume that a table named test has the following records:

Header id proc1 proc2 proc3
Records 12 3.4 6.7 12.4

The format is as follows:
Header id proc value
Record 12 proc1 3.4
Record 12 proc2 6.7
Record 12 proc3 12.4

Method 1: UseUnion allMethod (this method will become long as the number of fields increases and is not recommended)

1. SelectId,'Proc1', Proc1
2. FromTestjacWhereId = 12
3. Union All
4. SelectId,'Proc2', Proc2
5. FromTestjacWhereId = 12
6. Union All
7. SelectId,'Proc3', Proc3
8. FromTestjacWhereId = 12;

1. SelectId,'Proc1', Proc1FromTestjacWhereId = 12Union AllSelectId,'Proc2', Proc2FromTestjacWhereId = 12Union AllSelectId,'Proc3', Proc3from testjacWhereId = 12;

Method 2: decode + System View USER_TAB_COLS (Recommendation):

1. SelectA. id, B. column_name, decode (B. column_name,'Proc1', A. proc1,'Proc2', A. proc2,'Proc3', A. proc3,Null) Value
2. FromTest ,(SelectColumn_nameFromUser_tab_colsWhereColumn_id> 1AndTable_name ='Test') B