Summary of column and column conversion in Oracle

1) Row-to-column Conversion

Table Structure: Student

 
Create TableStudent (User_name Varchar2(20), CourseVarchar2(20), ScoreFloat)
 

Initialize data:

 Insert   Into Student ( User_name , Course, score) Values ('  Zhangyue  ' , '  Enlish  ' , 85  );  Insert   Into Student ( User_name , Course, score) Values ( '  Zhangyue  ' ,'  Math  ' , 75  );  Insert   Into Student ( User_name , Course, score) Values ( '  Zhangyue  ' , '  Chinese  ' ,97  );  Insert   Into Student ( User_name , Course, score) Values ( '  Zhangyue  ' , '  Physics  ' , 76  );  Insert  Into Student ( User_name , Course, score) Values ( '  Heshan  ' , '  Enlish  ' , 95  );  Insert   Into Student ( User_name , Course, score)Values ( '  Heshan  ' , '  Math  ' , 67  );  Insert   Into Student ( User_name , Course, score) Values ( '  Heshan ' , '  Physics  ' , 89  );  Insert   Into Student ( User_name , Course, score) Values ( '  Heshan  ' , '  Chinese ' , 79 );

The initial data is as follows:

 

To implement the following query:

 

This is the most common row-to-column conversion. The main principle is to use the decode function, clustering function (max), and group by grouping,The max aggregate function can also be used.Replace sum, Min, AVG, and other Aggregate functions.

SQLCodeAs follows:

 Select T. User_name  ,  Max (Decode (course, '  English ' , Score, 0 )) As  English,  Max (Decode (course, '  Math  ' , Score, 0 )) As  Math,  Max (Decode (course, '  Chinese  ' , Score, 0 )) As  Chinese,  Max (Decode (course, '  Physics  ' , Score, 0 )) As  Physics  From  Student t  Group   By T. User_name 

Or you are not using the decode function.

 Select T. User_name  ,  Max ( Case Course When   '  English  '   Then Score Else   0   End ) As  English, Max ( Case Course When   '  Math  '   Then Score Else   0   End ) As  Math,  Max ( Case Course When  '  Chinese  '   Then Score Else   0   End ) As  Chinese,  Max ( Case Course When   '  Physics  '   Then ScoreElse   0   End ) As  Physics  From  Student t  Group   By T. User_name 

2) convert multiple rows into strings

Table Structure: tb_name

 
Create TableTb_name (IDInt, RemarkVarchar(2))
 

Initialize data:

 Insert   Into Tb_name Values ( 1 , '  A  '  );  Insert   Into Tb_name Values ( 1 , '  B  ' );  Insert   Into Tb_name Values ( 1 , '  C  '  );  Insert   Into Tb_name Values ( 2 , '  A '  );  Insert   Into Tb_name Values ( 2 , '  D  '  );  Insert   Into Tb_name Values ( 2 , ' E  '  );  Insert   Into Tb_name Values ( 3 , '  C  '  );  Commit ;

The initial data is as follows:

If you need to implement the following query

Wm_concat is introduced in Oracle 10 GB and separated by commas (,) to join column values

The SQL statement is as follows:

SelectID, wm_concat (remark) remarkFromTb_name tGroup ByID
 

3) column-to-row

Table Structure:

Create TableTb_test (User_nameVarchar2(20), EnglishFloat, MathFloat, ChineseFloat, PhysicsFloat)
 

Initialize data:

 Insert   Into Tb_test ( User_name , English, math, Chinese, physics)  Values ( '  Zhangyue  ' , 85 , 75 , 97 , 76  );  Insert   Into Tb_test ( User_name , English, math, Chinese, physics)  Values ( '  Heshan  ' , 95 , 67 , 79 , 89  );  Commit ;

The initial data is as follows:

 

If you need to implement the following query

Here we will use the Union functionNote that the SELECT statement inside the Union must have the same number of columns. Columns must also have similar data types.

In addition, the columns in each select statement must be in the same order.

SQL statement:

 Select   User_name , '  Enlish  ' Course, English score From Tb_test t Union  Select   User_name , '  Math  ' Course, math scoreFrom Tb_test t Union  Select   User_name , '  Chinese  ' Course, Chinese score From Tb_test t Union  Select   User_name , '  Physics  ' Course, physics score From Tb_test t

4) convert multiple columns into strings

Table Structure:

 
Create TableTb_col (IDInteger, C1Varchar2(2), C2Varchar2(2), C3Varchar2(2))
 

Initialize data:

 Insert   Into  Tb_col (ID, C1, C2, C3) Values ( 1 , '  C1  ' , '  C2  ' , '  C3  '  );  Insert   Into  Tb_col (ID, C1, C2, C3)  Values (2 , '  C4  ' , '  C5  ' , '  C6  '  );  Insert   Into  Tb_col (ID, C1, C2, C3)  Values ( 3 ,'  C7  ' , '  C8  ' , '  C9  '  );  Commit ;

The initial data is as follows:

If you need to implement the following query

This is relatively simple and can be implemented using the | or Concat function:

The SQL statement is as follows:

SelectID, C1|','|C2|','|C3AsColFromTb_col t
 

 

The above is a simple summary. If you have any questions, please let us know so that I can learn. Thank you very much!