数据库表中的行列转换

表1：

表2：

行转列：

SELECT id1 AS id1 ,  
MAX(CASE  WHEN course='chinese' THEN score ELSE 0 END) chinese,  
MAX(CASE WHEN course ='math' THEN score ELSE 0 END) math,  
MAX(CASE  WHEN course ='english' THEN score ELSE 0 END) english  
FROM t  
GROUP BY id1  

列转行：

SELECT * FROM (  
SELECT id1, "chinese" AS 'course', chinese AS 分数 FROM t2 
UNION ALL  
SELECT id1, "math" AS 'course', math AS 分数 FROM t2  
UNION ALL  
SELECT id1,  "english" AS 'course', english AS 分数 FROM t2
  
) teptable