行数据转换为列数据
1、创建表和数据
CREATETABLEtmp (IDINTIDENTITY(1,1),NameNVARCHAR(50),ClassNVARCHAR(50),
ScoreINT)INSERTINTOtmp ( Name, Class, Score )VALUES(小亮,HTML5,95)INSERTINTOtmp ( Name, Class, Score )VALUES(小亮,CSS,98)INSERTINTOtmp ( Name, Class, Score )VALUES(小亮,JavaScript,92)INSERTINTOtmp ( Name, Class, Score )VALUES(小清,HTML5,98)INSERTINTOtmp ( Name, Class, Score )VALUES(小清,CSS,99)INSERTINTOtmp ( Name, Class, Score )VALUES(小清,JavaScript,97)
2、查看转换前数据结构
转换前
3、使用PIVOT函数进行转换
DECLARE@ClassNVARCHAR(50), @SqlTextNVARCHAR(500);SELECT@Class=STUFF((SELECT,+ClassFROMtmp WHERE Name = t.Name FOR XML PATH()) , 1 , 1 , ) FROM tmp t GROUP BY NameSET@SqlText =SELECT a.*
FROM (
SELECT Name, Class, Score
FROM tmp
) t
PIVOT (
MAX(Score) FOR Class IN (+ @Class+)
) a;
EXEC(@SqlText)
4、或 使用CASE WHEN 方法进行转换
SELECTName,MAX(CASEWHENClass=HTML5THENScoreELSE0END)ASHTML5,MAX(CASEWHENClass=CSSTHENScoreELSE0END)ASCSS,MAX(CASEWHENClass=JavaScriptTHENScoreELSE0END)ASJavaScriptFROMtmpGROUPBYName
5、查看转换后数据结构
转换后
列数据转换为行数据
1、创建表和数据
CREATETABLEtmp (IDINTIDENTITY(1,1),NameNVARCHAR(50),
HTML5INT,
CSSINT,
JavaScriptINT)INSERTINTOtmp ( Name, HTML5, CSS, JavaScript )VALUES(小亮,95,98,82)INSERTINTOtmp ( Name, HTML5, CSS, JavaScript )VALUES(小清,98,99,97)
2、查看转换前数据结构
转换前
3、使用UNPIVOT函数进行转换
SELECTa.*FROM(SELECTName, HTML5, CSS, JavaScriptFROMtmp) tUNPIVOT(
ScoreFORClassIN(HTML5, CSS, JavaScript)
) a
4、查看转换后数据结构
转换后
