Example of reciprocal transfer between a vertical table and a horizontal table (RPM)

1. longitudinal table to Horizontal table:

Longitudinal structure: Table1

Converted cross-table structure:

SQL Sample code:

1234567891011

selectusername,sum(caseCourse when‘语文‘thenGrade else0 end) as语文,sum(caseCourse when‘数学‘thenGrade else0 end) as数学,sum(caseCourse when‘英语‘thenGrade else0 end) as 英语fromTable1groupbyusername

2, the horizontal table to the longitudinal table :

Horizontal Table structure: TableA
ID name Chinese Math english
1 Sheets 380 90 70
2 Lee 490 85 95
3 Wang 588 75 90

the converted longitudinal table structure:
ID Name Account Score
1 in three languages 80
2 Zhang Three Mathematics 90
3 three English 70
4 John Doe Language 90
5 John Doe Mathematics 80
6 John Doe English 99
7 Harry Language 85
8 Harry Mathematics 96
9 Harry English 88

SQL Sample code:

123	SELECT姓名,‘语文‘AS科目,语文   AS成绩   FROMTableA UNIONALLSELECT姓名,‘数学‘AS科目,数学   AS成绩   FROMTableA UNIONALLSELECT姓名,‘英语‘AS 科目,英语   AS成绩   FROMTableA ORDERBY姓名,科目 DESC;

Case variable Expression--judging a ' variable expression '
When value-when ' variable expression ' is a ' value '
Then return value expression--Returns the value of the ' return value expression '
[When ...
Then ...
.....] --Multiple judgments can be made
[Else other case return value expression]--not conforming to all when there is another case.
End--Ending

Example of reciprocal transfer between a vertical table and a horizontal table (RPM)