How to convert MySQL horizontally to vertically and vertically to horizontally

How to convert MySQL horizontally to vertically and vertically to horizontally

Initialize Data

DROP TABLE IF EXISTS `test_01`;
CREATE TABLE `test_01` (
 `id` int(0) NOT NULL,
 `user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'User',
 `km` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'Subject',
 `fs` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'Score',
 `time` datetime(0) NULL DEFAULT NULL COMMENT 'time',
 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `test_01` VALUES (1, '小三', '语文', '98', '2020-08-06 15:51:21');
INSERT INTO `test_01` VALUES (2, '小三', '数学', '90', '2020-07-01 15:51:25');
INSERT INTO `test_01` VALUES (3, '小三', '英语', '77', '2020-06-01 15:51:28');
INSERT INTO `test_01` VALUES (4, '小等', '英语', '78', '2020-06-01 15:51:28');

1. Horizontal to vertical arrangement

SELECT 
	user,
	SUM( CASE WHEN km = "Chinese" THEN fs ELSE 0 END ) "Chinese",
	SUM( CASE WHEN km = "Mathematics" THEN fs ELSE 0 END ) "Mathematics",
	SUM( CASE WHEN km = "English" THEN fs ELSE 0 END ) "English" 
FROM
	test_01 
GROUP BY user

insert image description here

2. Vertical to horizontal arrangement

SELECT km FROM test_01 WHERE id = 1 
UNION 
SELECT fs FROM test_01 WHERE id = 1 

insert image description here

This is the end of this article about how to convert MySQL horizontally to vertically and vertically to horizontally. For more information about how to convert MySQL horizontally to vertically and vertically to horizontally, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • MySQL horizontal and vertical table conversion operation implementation method

<<:  Detailed tutorial on distributed operation of jmeter in docker environment

>>:  Implementation of fastdfs+nginx cluster construction

Recommend

Introduction to the process of installing MySQL 8.0 in Linux environment

Table of contents Preface 1. Linux changes the yu...

How to create an Nginx server with Docker

Operating environment: MAC Docker version: Docker...

VMware virtualization kvm installation and deployment tutorial summary

Virtualization 1. Environment Centos7.3 Disable s...

How to disable foreign key constraint checking in MySQL child tables

Prepare: Define a teacher table and a student tab...

Record of the actual process of packaging and deployment of Vue project

Table of contents Preface 1. Preparation - Server...

Explain TypeScript enumeration types in detail

Table of contents 1. Digital Enumeration 2. Strin...

How to use MySQL DATEDIFF function to get the time interval between two dates

describe Returns the time interval between two da...

Some questions about hyperlinks

<br />I am very happy to participate in this...

Detailed explanation of MySQL 5.7.9 shutdown syntax example

mysql-5.7.9 finally provides shutdown syntax: Pre...

JavaScript implements class lottery applet

This article shares the specific code of JavaScri...

Detailed steps to install the specified version of docker (1.12.6) using rpm

1. Reasons If the system is Centos7.3, the Docker...

JavaScript to implement a simple clock

This article example shares the specific code for...

A brief discussion on the design of Tomcat multi-layer container

Table of contents Container Hierarchy The process...

Win7 installation MySQL 5.6 tutorial diagram

Table of contents 1. Download 2. Installation 3. ...

Introduction to CSS BEM Naming Standard (Recommended)

1 What is BEM Naming Standard Bem is the abbrevia...