How to convert rows to columns in mysql

Mysql row to column method: 1. Use the "SUM(CASE table name WHEN field name THEN score ELSE 0 END) as field name" operation conversion; 2. Use "SUM(IF(table name = field name,score,0)) as field name" operation conversion.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to convert mysql rows to columns

Row to column

That is, the different contents of multiple rows under the same column are used as multiple fields and the corresponding contents are output.

Create table statement

DROP TABLE IF EXISTS tb_score;
CREATE TABLE tb_score(
    id INT(11) NOT NULL auto_increment,
    userid VARCHAR(20) NOT NULL COMMENT '用户id',
    subject VARCHAR(20) COMMENT '科目',
    score DOUBLE COMMENT '成绩',
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

Insert data

INSERT INTO tb_score(userid,subject,score) VALUES ('001','语文',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','数学',92);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','英语',80);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','语文',88);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','数学',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','英语',75.5);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','语文',70);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','数学',85);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','英语',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);

Query the content in the data table (that is, the result before conversion)

SELECT * FROM tb_score

Let’s take a look at the conversion first The final result:

#It can be seen that the row conversion here is to select the multiple rows of the original subject field as different columns in the result set, and based on the userid Group and display the corresponding scores.

1. Use case...when....then to convert rows to columns

SELECT userid,
SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文',
SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学',
SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语',
SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治' 
FROM tb_score 
GROUP BY userid

2. Use IF() to convert rows to columns:

SELECT userid,
SUM(IF(`subject`='语文',score,0)) as '语文',
SUM(IF(`subject`='数学',score,0)) as '数学',
SUM(IF(`subject`='英语',score,0)) as '英语',
SUM(IF(`subject`='政治',score,0)) as '政治' 
FROM tb_score 
GROUP BY userid

Note:

(1) SUM() is to be able to use GROUP BY to group according to userid, because the record of subject="language" corresponding to each userid is only One record, so the value of SUM() is equal to the score value of the corresponding record.

If there are two records with userid ='001' and subject='中文', then the value of SUM() at this time will be the sum of these two records. Similarly, use Max() The value will be the largest value among the two records. But under normal circumstances, a user has only one score corresponding to a subject, so aggregate functions such as SUM(), MAX(), MIN(), and AVG() can be used to achieve the effect of row conversion.

(2)IF(`subject`='中文',score,0) as a condition, that is, SUM(), MAX(), MIN( ), AVG() operation, if score has no value, it defaults to 0.

3. Use SUM(IF()) to generate columns WITH ROLLUP to generate summary rows, and use IFNULL to display the summary row title as Total

SELECT IFNULL(userid,'total') AS userid,
SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(IF(`subject`='total',score,0)) AS total
FROM(
    SELECT userid,IFNULL(`subject`,'total') AS `subject`,SUM(score) AS score
    FROM tb_score
    GROUP BY userid,`subject`
    WITH ROLLUP
    HAVING userid IS NOT NULL
)AS A 
GROUP BY userid
WITH ROLLUP;

Run results:

##4. Use SUM(IF()) to generate columns UNION to generate summary rows, and use IFNULL to display the summary row title as Total

SELECT userid,
SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(score) AS TOTAL 
FROM tb_score
GROUP BY userid
UNION
SELECT 'TOTAL',SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(score) FROM tb_score

Running results:

5. Use SUM(IF()) to generate columns and directly generate results without using subqueries

SELECT IFNULL(userid,'TOTAL') AS userid,
SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(score) AS TOTAL 
FROM tb_score
GROUP BY userid WITH ROLLUP;

Run result:

6. Dynamic, suitable for column uncertainty situation

SET @EE='';
select @EE :=CONCAT(@EE,'sum(if(subject= \'',subject,'\',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM tb_score) A ;
SET @QQ = CONCAT('select ifnull(userid,\'TOTAL\')as userid,',@EE,' sum(score) as TOTAL from tb_score group by userid WITH ROLLUP');
-- SELECT @QQ;
PREPARE stmt FROM @QQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Run result:

7. Merge field display: use group_concat()

SELECT userid,GROUP_CONCAT(`subject`,":",score)AS 成绩 FROM tb_score
GROUP BY userid

Running results:

group_concat(), manual description: This function returns a string result with non-NULL values ​​​​from the connection of a group.

is rather abstract and difficult to understand. To understand it in a simple way, it is actually like this: group_concat() will calculate which rows belong to the same group and display the columns belonging to the same group. Which columns to return are determined by the function parameters (field names). There must be a standard for grouping, which is to group according to the column specified by group by.

Conclusion: The group_concat() function can convert multiple rows belonging to the same group into one column.

Recommended learning:

mysql video tutorial

The above is the detailed content of How to convert rows to columns in mysql. For more information, please follow other related articles on the PHP Chinese website!