SQL Server 動(dòng)態(tài)行轉(zhuǎn)列(參數(shù)化表名、分組列、行轉(zhuǎn)列字段、字段值)

1、SQLServer動(dòng)態(tài)行轉(zhuǎn)列（參數(shù)化表名、分組列、行轉(zhuǎn)列字段、字段值）本文所涉及的內(nèi)容（Contents）背景（Contexts）實(shí)現(xiàn)代碼（SQLCodes）方法一：使用拼接SQL,靜態(tài)列字段；方法二：使用拼接SQL,動(dòng)態(tài)列字段；方法三：使用PIVOT關(guān)系運(yùn)算符，靜態(tài)列字段；方法四：使用PIVOT關(guān)系運(yùn)算符，動(dòng)態(tài)列字段；擴(kuò)展閱讀一：參數(shù)化表名、分組列、行轉(zhuǎn)列字段、字段值擴(kuò)展閱讀二：在前面的基礎(chǔ)上加入條件過濾；其實(shí)行轉(zhuǎn)列并不是一個(gè)什么新鮮的話題了，甚至已經(jīng)被大家說到爛了，網(wǎng)上的很多例子多多少少都有些問題，所以我希望能讓大家快速的看到執(zhí)行的效果，所以在動(dòng)態(tài)列的基礎(chǔ)上再把表、分組字段、行轉(zhuǎn)列字段、值

2、這四個(gè)行轉(zhuǎn)列固定需要的值變成真正意義的參數(shù)化，大家只需要根據(jù)自己的環(huán)境，設(shè)置參數(shù)值，馬上就能看到效果了。行轉(zhuǎn)列的效果圖如圖1所示：1idTSource227Q更由暹文3E王五17009044王五75厶一2BC5755王五57z3Q1000Ci膠77張三頁謂100行轉(zhuǎn)列削行鶉列后（圖1：行轉(zhuǎn)列效果圖）（一）首先我們先創(chuàng)建一個(gè)測試表，往里面插入測試數(shù)據(jù)，返回表記錄如圖2所示:創(chuàng)建測試表IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(Ndbo.TestRows2Columns)ANDtypein(NU)DROPTABLEdbo.Test

3、Rows2ColumnsGOCREATETABLEdbo.TestRows2Columns(IdintIDENTITY(1,1)NOTNULL,UserNamenvarchar(50)NULLSubjectnvarchar(50)NULL,Sourcenumeric(18,0)NULL)ONPRIMARYGO插入測試數(shù)據(jù)INSERTINTOTestRows2ColumnsSELECTN張三,N語文,60SELECTN，李四,N數(shù)學(xué),70SELECTN王五,N英語,80SELECTN王五,N數(shù)學(xué),75SELECTN王五,N語文,57SELECTN，李四,N語文,80SELECTN張三,N英語,1

4、00GO(UserName,Subject,Source)UNIONALLUNIONALLUNIONALLUNIONALLUNIONALLUNIONALLSELECT*FROMTestRows2ColumnsIdUserNameSubjectSource11張三語文22李四數(shù)學(xué)703I3I王五英語SO4上王五數(shù)學(xué)7555王五語文5766李四語文SC77張三英語D/（圖2:樣本數(shù)據(jù)）（二）先以靜態(tài)的方式實(shí)現(xiàn)行轉(zhuǎn)列，效果如圖3所示:1：靜態(tài)拼接行轉(zhuǎn)列SELECTUserName,SUM(CASESubjectWHEN數(shù)學(xué)THENSourceELSE0END)AS數(shù)學(xué)SUM(CASESubjectW

5、HEN英語THENSourceELSE0END)AS英語SUM(CASESubjectWHEN語文THENSourceELSE0END)AS語文FROMTestRows2ColumnsGROUPBYUserNameGOUserName數(shù)學(xué)英語語文1李四700802住五80573張三a（圖3:樣本數(shù)據(jù)）（三）接著以動(dòng)態(tài)的方式實(shí)現(xiàn)行轉(zhuǎn)列，這是使用拼接SQL的方式實(shí)現(xiàn)的，所以它適用于SQLServer2000以上的數(shù)據(jù)庫版本，執(zhí)行腳本返回的結(jié)果如圖2所示；-2：動(dòng)態(tài)拼接行轉(zhuǎn)列DECLAREsqlVARCHAR(8000)SETsql=SELECTUserName,SELECTsql=sql+SUM(

6、CASESubjectWHEN+Subject+THENSourceELSE0END)AS+QUOTENAME(SubjectFROM(SELECTDISTINCTSubjectFROMTestRows2Columns)ASaSELECTsql=LEFT(sql,LEN(sql)-1)+FROMTestRows2ColumnsGROUPBYUserNamePRINT(sql)EXEC(sql)GO（四）在SQLServer2005之后有了一個(gè)專門的PIVOT和UNPIVOT關(guān)系運(yùn)算符做行列之間的轉(zhuǎn)換，下面是靜態(tài)的方式實(shí)現(xiàn)的，實(shí)現(xiàn)效果如圖4所示：3:靜態(tài)PIVOT行轉(zhuǎn)列SELECT*FROM(S

7、ELECTUserNameSubjectSourceFROMTestRows2Columns)pPIVOT(SUM(Source)FORSubjectIN(數(shù)學(xué),英語,語文)ASpvtORDERBYpvt.UserName;GO數(shù)學(xué)英語語文1李四70NULL802王7580573船NUU-叫點(diǎn)園（圖4）（五）把上面靜態(tài)的SQL基礎(chǔ)上進(jìn)行修改，這樣就不用理會(huì)記錄里面存儲了什么，需要轉(zhuǎn)成什么列名的問題了，腳本如下，效果如圖4所示：4:動(dòng)態(tài)PIVOT行轉(zhuǎn)列DECLAREsql_strVARCHAR(8000)DECLAREsql_colVARCHAR(8000)SELECTsql_col=ISNUL

8、L(sql_col+QUOTENAME(Subject)FROMTestRows2ColumnsGROUPBYSubjectSETsql_str=SELECT*FROM(SELECTUserName,Subject,SourceFROMTestRows2Columns)pPIVOT(SUM(Source)FORSubjectIN(+sql_col+)ASpvtORDERBYpvt.UserNamePRINT(sql_str)EXEC(sql_str)（六）也許很多人到了上面一步就夠了，但是你會(huì)發(fā)現(xiàn)，當(dāng)別人拿到你的代碼，需要不斷的修改成他自己環(huán)境中表名、分組列、行轉(zhuǎn)列字段、字段值這幾個(gè)參數(shù)，邏輯

9、如圖5所示，所以，我繼續(xù)對上面的腳本進(jìn)行修改，你只要設(shè)置自己的參數(shù)就可以實(shí)現(xiàn)行轉(zhuǎn)列了，效果如圖4所示:2.方組列3.行轉(zhuǎn)列宇段4.宇段值5:參數(shù)化動(dòng)態(tài)PIVOT行轉(zhuǎn)列Author:聽風(fēng)吹雨Createdate:DECLAREsql_strNVARCHAR(MAX)DECLAREsql_colNVARCHAR(MAX)DECLAREtableNameSYSNAME行轉(zhuǎn)列表DECLAREgroupColumnSYSNAME分組字段DECLARErow2columnSYSNAME行變列的字段DECLARErow2columnValueSYSNAME行變列值的字段SETtableName=TestRo

10、ws2ColumnsSETgroupColumn=UserNameSETrow2column=SubjectSETrow2columnValue=Source-從行數(shù)據(jù)中獲取可能存在的列SETsql_str=NSELECTsql_col_out=ISNULL(sql_col_out+,)+QU0TENAME(+row2column+)FROM+tableName+GROUPBY+row2column+PRINTsql_strEXECsp_executesqlsql_str,Nsql_col_outNVARCHAR(MAX)OUTPUT,sql_col_out=sql_colOUTPUTPRIN

11、Tsql_colSETsql_str=NSELECT*FROM(SELECT+groupColumn+,+row2column+,+row2columnValue+FROM+tableName+)pPIVOT(SUM(+row2columnValue+)FOR+row2column+IN(+sql_col+)ASpvtORDERBYpvt.+groupColumn+PRINT(sql_str)EXEC(sql_str)p，flSl1*fltHtr*fs.lyrrtiricfriiRa巧jUwrftane:Sufatect:jScuce.11瑯三丨語文網(wǎng)2E藪學(xué)：7D3:英語!：8D4s王五:曲

12、寧=J7555任五i譜文:j5766；竽四：8Q77i站Atvsttisn_:英語:T-TTTTTTThoo1表容IestRmvs2Columns(圖5)（七）在實(shí)際的運(yùn)用中，我經(jīng)常遇到需要對基礎(chǔ)表的數(shù)據(jù)進(jìn)行篩選后再進(jìn)行行轉(zhuǎn)列，那么下面的腳本將滿足你這個(gè)需求，效果如圖6所示：6：帶條件查詢的參數(shù)化動(dòng)態(tài)PIVOT行轉(zhuǎn)列Author:聽風(fēng)吹雨Createdate:DECLAREsql_strNVARCHAR(MAX)DECLAREsql_colNVARCHAR(MAX)DECLAREsql_whereNVARCHAR(MAX)DECLAREtableNameSYSNAME行轉(zhuǎn)列表DECLAREgr

13、oupColumnSYSNAME分組字段DECLARErow2columnSYSNAME行變列的字段DECLARErow2columnValueSYSNAME行變列值的字段SETtableName=TestRows2ColumnsSETgroupColumn=UserNameSETrow2column=SubjectSETrow2columnValue=SourceSETsql_where=WHEREUserName=王五，-從行數(shù)據(jù)中獲取可能存在的列SETsql_str=NSELECTsql_col_out=ISNULL(sql_col_out+，,，,，)+QU0TENAME(+row2column+)FROM+tableName+sql_where+GROUPBY+row2column+PRINTsql_strEXECsp_executesqlsql_str,Nsql_col_outNVARCHAR(MAX)OUTPUT,sql_col_out=sql_colOUTPUTPRINTsql_colSETs