行列转换

行转列： 

 

 

方法一：

--使用之前要1、去重复
SELECT USERNAME,
MAX(CASE SUBJECT WHEN '语文' THEN SCORE ELSE NULL END ) AS 语文,
MAX(CASE SUBJECT WHEN '数学' THEN SCORE ELSE NULL END ) AS 数学,
MAX(CASE SUBJECT WHEN '英语' THEN SCORE ELSE NULL END ) AS 英语,
MAX(CASE SUBJECT WHEN '生物' THEN SCORE ELSE NULL END ) AS 生物
FROM SS
GROUP BY USERNAME

 方法二：

--使用之前要1、变成完美的数据  2、去重复
SELECT 
t1.USERNAME,
t1.SCORE 语文,
t2.SCORE 数学,
t3.SCORE 英语,
t4.SCORE 生物
FROM
(SELECT * FROM SS WHERE SUBJECT='语文') t1
LEFT JOIN 
(SELECT * FROM SS WHERE SUBJECT='数学') t2
ON t1.USERNAME = t2.USERNAME
LEFT JOIN 
(SELECT * FROM SS WHERE SUBJECT='英语') t3
ON t1.USERNAME = t3.USERNAME
LEFT JOIN 
(SELECT * FROM SS WHERE SUBJECT='生物') t4
ON t1.USERNAME = t4.USERNAME

方法三、

--sql的行转列(PIVOT)与列转行(UNPIVOT)

方法四、

 

转载于:https://www.cnblogs.com/revo/p/9770121.html