mysql如何实现动态列转行

Question

如下建表.
由于实际日期date列很多，如何动态的将date列转为行，并实现如下显示效果。

```sql
DROP TABLE IF EXISTS tb_score;

CREATE TABLE tb_score(
    id INT(11) NOT NULL auto_increment,
    userid VARCHAR(20) NOT NULL COMMENT '用户id',
    name VARCHAR(20) COMMENT '姓名',
    week VARCHAR(20) COMMENT '周期',
    subject VARCHAR(20) COMMENT '科目',
    score DOUBLE COMMENT '成绩',
    date DATE '日期',
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;


INSERT INTO tb_score(userid,subject,score) VALUES ('001','zhang','周三','语文','90','2023-11-01');
INSERT INTO tb_score(userid,subject,score) VALUES ('001','zhang','周四','数学','90','2023-11-02');
INSERT INTO tb_score(userid,subject,score) VALUES ('001','zhang','周五','英语','90','2023-11-03');
INSERT INTO tb_score(userid,subject,score) VALUES ('001','li','周三','语文','90','2023-11-01');
INSERT INTO tb_score(userid,subject,score) VALUES ('001','li','周四','数学','90','2023-11-02');
INSERT INTO tb_score(userid,subject,score) VALUES ('001','li','周五','英语','90','2023-11-03');
INSERT INTO tb_score(userid,subject,score) VALUES ('001','lee','周三','语文','90','2023-11-01');
INSERT INTO tb_score(userid,subject,score) VALUES ('001','lee','周四','数学','90','2023-11-02');
INSERT INTO tb_score(userid,subject,score) VALUES ('001','lee','周五','英语','90','2023-11-03');
INSERT INTO tb_score(userid,subject,score) VALUES ('001','lee','周六','政治','90','2023-11-04');


![img](https://img-mid.csdnimg.cn/release/static/image/mid/ask/59408090799613.png "#left")

```

Answer 1

可以看这篇文章，有详细的分析过程，有疑问给我发消息：动态行转列：处理不确定数量的行转列操作