sqlvarchar转int 「sql文本转数值函数」

行数据转换为列数据

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、查看转换后数据结构

转换后