表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