SELECT *
FROM student
PIVOT (
SUM(score) FOR subject IN (语文, 数学, 英语)
)
或者
SELECT name,
MAX(
CASE
WHEN subject=‘语文’
THEN score
ELSE 0
END) AS “语文”,
MAX(
CASE
WHEN subject=‘数学’
THEN score
ELSE 0
END) AS “数学”,
MAX(
CASE
WHEN subject=‘英语’
THEN score
ELSE 0
END) AS “英语”
FROM student
GROUP BY name
反过来:
SELECT *
FROM student1
UNPIVOT (
score FOR subject IN (“语文”,“数学”,“英语”)
)
或者
SELECT
NAME,
‘语文’ AS subject ,
MAX(“语文”) AS score
FROM student1 GROUP BY NAME
UNION
SELECT
NAME,
‘数学’ AS subject ,
MAX(“数学”) AS score
FROM student1 GROUP BY NAME
UNION
SELECT
NAME,
‘英语’ AS subject ,
MAX(“英语”) AS score
FROM student1 GROUP BY NAME