introduction

In learning sql, I have encountered the problems of column to row and row to column. Here is a summary of how to solve different problems in the corresponding scenario;

Column to row

Create a table stu_score_01:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for stu_score_01
-- ----------------------------
DROP TABLE IF EXISTS `stu_score_01`;
CREATE TABLE `stu_score_01` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `chinese` varchar(255) DEFAULT NULL,
  `math` varchar(255) DEFAULT NULL,
  `english` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of stu_score_01
-- ----------------------------
BEGIN;
INSERT INTO `stu_score_01` VALUES ('1', 'Zhang San', '111', '109', '98');
INSERT INTO `stu_score_01` VALUES ('2', 'Li Si', '89', '119', '109');
INSERT INTO `stu_score_01` VALUES ('3', 'Wang Wu', '96', '102', '107');
INSERT INTO `stu_score_01` VALUES ('4', 'Xiao Liu', '56', '78', '88');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

If you want to convert this table to the following form:

+--------+---------+-------+
| name   | project | score |
+--------+---------+-------+
| Zhang San 	| chinese | 111   |
| Li Si 	| chinese | 89    |
| Wang Wu 	| chinese | 96    |
| Xiao Liu 	| chinese | 56    |
| Zhang San 	| math    | 109   |
| Li Si 	| math    | 119   |
| Wang Wu 	| math    | 102   |
| Xiao Liu 	| math    | 78    |
| Zhang San 	| english | 98    |
| Li Si 	| english | 109   |
| Wang Wu 	| english | 107   |
| Xiao Liu 	| english | 88    |
+--------+---------+-------+

Then you can use union or union all to realize column to row conversion:

select name, 'chinese' as project, chinese as score from stu_score_01
union all
select name, 'math' as project, math as score from stu_score_01
union all
select name, 'english' as project, english as score from stu_score_01;

A brief explanation: query all the information of each account separately, and then find the Union; For example, execute sql separately:

select name, 'chinese' as project, chinese as score from stu_score_01;

#result
+--------+---------+-------+
| name   | project | score |
+--------+---------+-------+
| Zhang San 	| chinese | 111   |
| Li Si 	| chinese | 89    |
| Wang Wu 	| chinese | 96    |
| Xiao Liu 	| chinese | 56    |
+--------+---------+-------+

Next, you only need to deduce the union of all case sets once;

Union and union all are union sets of tables, but union will have de duplication and sorting operations, and the efficiency is lower than that of union all. There is no need to de duplication here, so union all is used to ensure efficiency;

Row to column

Create a table stu_score_03:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for stu_score_03
-- ----------------------------
DROP TABLE IF EXISTS `stu_score_03`;
CREATE TABLE `stu_score_03` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `project` varchar(255) DEFAULT NULL,
  `score` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of stu_score_03
-- ----------------------------
BEGIN;
INSERT INTO `stu_score_03` VALUES ('1', 'Zhang San', 'chinese', '111');
INSERT INTO `stu_score_03` VALUES ('10', 'Li Si', 'english', '109');
INSERT INTO `stu_score_03` VALUES ('11', 'Wang Wu', 'english', '107');
INSERT INTO `stu_score_03` VALUES ('12', 'Xiao Liu', 'english', '88');
INSERT INTO `stu_score_03` VALUES ('2', 'Li Si', 'chinese', '89');
INSERT INTO `stu_score_03` VALUES ('3', 'Wang Wu', 'chinese', '96');
INSERT INTO `stu_score_03` VALUES ('4', 'Xiao Liu', 'chinese', '56');
INSERT INTO `stu_score_03` VALUES ('5', 'Zhang San', 'math', '109');
INSERT INTO `stu_score_03` VALUES ('6', 'Li Si', 'math', '119');
INSERT INTO `stu_score_03` VALUES ('7', 'Wang Wu', 'math', '102');
INSERT INTO `stu_score_03` VALUES ('8', 'Xiao Liu', 'math', '78');
INSERT INTO `stu_score_03` VALUES ('9', 'Zhang San', 'english', '98');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

If you want to convert each row of accounts into different columns separately, for example:

+--------+---------+------+---------+
| name   | chinese | math | english |
+--------+---------+------+---------+
| Xiao Liu 	| 56      | 78   | 88      |
| Zhang San 	| 111     | 109  | 98      |
| Li Si 	| 89      | 119  | 109     |
| Wang Wu 	| 96      | 102  | 107     |
+--------+---------+------+---------+

You can use case... when and max/sum and group by to achieve:

select name,
max(case when project = 'chinese' then score else 0 end) as 'chinese',
max(case when project = 'math' then score else 0 end) as 'math',
max(case when project = 'english' then score else 0 end) as 'english'
from stu_score_03
group by name;

# Or use sum

select name,
sum(case when project = 'chinese' then score else 0 end) as 'chinese',
sum(case when project = 'math' then score else 0 end) as 'math',
sum(case when project = 'english' then score else 0 end) as 'english'
from stu_score_03
group by name;

Briefly explain:

• Because you want to query everyone's grades in different subjects, you need to group different people, so you need to use group by, otherwise no one knows whose grades are found;

• For each case when, for example: case when project = 'chinese' then score else 0 end

  It means that when the project is chinese, get the score, otherwise get 0; Others mean the same

• Also, why do you need to add max or sum? First imagine the effect if you don't add max or sum:

  Because the chinese subject is judged first. If Zhang San's first subject is math, he will judge the chinese subject first, because math is not equal to chinese,

  Therefore, assign 0 to the chinese account;

  Therefore, you will see the following effects:

  select name,
  case when project = 'chinese' then score else 0 end as 'chinese',
  case when project = 'math' then score else 0 end as 'math',
  case when project = 'english' then score else 0 end as 'english'
  from stu_score_03
  group by name;
  
  #results of enforcement
  
  +--------+---------+------+---------+
  | name   | chinese | math | english |
  +--------+---------+------+---------+
  | Xiao Liu 	| 0       | 0    | 88      |
  | Zhang San 	| 111     | 0    | 0       |
  | Li Si 	| 0       | 0    | 109     |
  | Wang Wu 	| 0       | 0    | 107     |
  +--------+---------+------+---------+
  

  Because english scores first appeared in grade six, his chinese and math scores were given a value of 0,

  Zhang San's first achievement is chinese, so his math and english scores are also given a value of 0;

  If max or sum is used, max will go to the maximum value in the case of all values (including 0), which is actually the actual score, but the case of 0 is removed;

  Sum is the sum of all values, because all values except the actual score are 0, so it can be added directly;

summary

Having said so much, we can actually sum up two sentences:

For column to row conversion, union or union all is used to find the union set of each column of data required for query

Row to column, use case... when to query data by situation, and filter by group by and sum/max

Welcome to share your views and opinions!!