SELECT 
    id,
    MAX(CASE WHEN subject = '數(shù)學' THEN score ELSE NULL END) AS '數(shù)學',
    MAX(CASE WHEN subject = '語文' THEN score ELSE NULL END) AS '語文',
    MAX(CASE WHEN subject = '英語' THEN score ELSE NULL END) AS '英語'
FROM student_scores
GROUP BY id;

SELECT 
    id,
    MAX(IF(subject = '數(shù)學', score, NULL)) AS '數(shù)學',
    MAX(IF(subject = '語文', score, NULL)) AS '語文',
    MAX(IF(subject = '英語', score, NULL)) AS '英語'
FROM student_scores
GROUP BY id;

SELECT 
    id,
    JSON_UNQUOTE(JSON_EXTRACT(pivot_data, '$.數(shù)學')) AS '數(shù)學',
    JSON_UNQUOTE(JSON_EXTRACT(pivot_data, '$.語文')) AS '語文',
    JSON_UNQUOTE(JSON_EXTRACT(pivot_data, '$.英語')) AS '英語'
FROM (
    SELECT 
        id,
        JSON_OBJECTAGG(subject, score) AS pivot_data
    FROM student_scores
    GROUP BY id
) AS t;

select  id
        ,keyvalue(column_value,'name') as name
        ,keyvalue(column_value,'age') as age
from    (
            select  id
                    ,wm_concat(';',concat(obj_name,':',obj_value)) as column_value
            from    school
            group by id
        ) 
;

-- 如果值存在英文冒號，導致取值為空的原因，看下面兩個sql例子即可理解
-- 返回null
select keyvalue('name:小紅:3737;age:13','name');
-- 返回3737
select keyvalue('name:小紅:3737;age:13','name:小紅');

SELECT id, '數(shù)學' AS subject, 數(shù)學 AS score FROM student_scores_pivot
UNION ALL
SELECT id, '語文' AS subject, 語文 AS score FROM student_scores_pivot
UNION ALL
SELECT id, '英語' AS subject, 英語 AS score FROM student_scores_pivot
ORDER BY id, subject;

SELECT 
    s.id,
    c.subject,
    CASE c.subject
        WHEN '數(shù)學' THEN s.數(shù)學
        WHEN '語文' THEN s.語文
        WHEN '英語' THEN s.英語
    END AS score
FROM student_scores_pivot s
CROSS JOIN (
    SELECT '數(shù)學' AS subject UNION ALL
    SELECT '語文' UNION ALL
    SELECT '英語'
) c;

SELECT 
    s.id,
    c.subject,
    CASE c.subject
        WHEN '數(shù)學' THEN s.數(shù)學
        WHEN '語文' THEN s.語文
        WHEN '英語' THEN s.英語
    END AS score
FROM student_scores_pivot s
CROSS JOIN (
	values
    row('數(shù)學') 
    ,row('語文')
    ,row('英語')
) c(subject);

SELECT 
    id,
    jt.subject,
    jt.score
FROM student_scores_pivot,
JSON_TABLE(
    JSON_OBJECT(
        '數(shù)學', 數(shù)學,
        '語文', 語文,
        '英語', 英語
    ),
    '$.*' COLUMNS(
        subject VARCHAR(10) PATH '$.key',
        score INT PATH '$.value'
    )
) AS jt;

DELIMITER //
CREATE PROCEDURE dynamic_pivot(IN table_name VARCHAR(100), IN row_id VARCHAR(100), IN pivot_col VARCHAR(100), IN value_col VARCHAR(100))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE col_name VARCHAR(100);
    DECLARE col_list TEXT DEFAULT '';
    DECLARE cur CURSOR FOR 
        SELECT DISTINCT pivot_col FROM table_name;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO col_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET col_list = CONCAT(col_list, 
            IF(col_list = '', '', ', '), 
            'MAX(CASE WHEN ', pivot_col, ' = ''', col_name, ''' THEN ', value_col, ' ELSE NULL END) AS `', col_name, '`');
    END LOOP;
    CLOSE cur;
    
    SET @sql = CONCAT('SELECT ', row_id, ', ', col_list, ' FROM ', table_name, ' GROUP BY ', row_id, ';');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 調(diào)用存儲過程
CALL dynamic_pivot('student_scores', 'id', 'subject', 'score');

-- 創(chuàng)建表
create table if not exists school (
`id` string,
`obj_name` string,
`obj_value` string
);

-- 插入測試數(shù)據(jù)
insert into school
values 
('1','name','小明'),
('1','age','12'),
('2','name','小紅'),
('2','age','13')
;


-- 列轉(zhuǎn)行
select  id
        ,keyvalue(column_value,'name') as name
        ,keyvalue(column_value,'age') as age
from    (
            select  id
                    ,wm_concat(';',concat(obj_name,':',obj_value)) as column_value
            from    school
            group by id
        ) 
;