资源描述：
《sqlserver结果集转为字符串》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、比如有语句select username from Employee返回结果是：username张三李四王五怎么输出一个字符串类似：张三，李四，王五答：declare@svarchar(1000)select@s=isnull(@s+',','')+usernamefromEmployeeselect@sasusername----------------------------------------------------------------------------Author:htl258(Tony)--Date:2010-03-3100:38:11--Version:
2、MicrosoftSQLServer2008(RTM)-10.0.1600.22(IntelX86)--Jul9200814:43:34--Copyright(c)1988-2008MicrosoftCorporation--DeveloperEditiononWindowsNT5.1(Build2600:ServicePack2)---------------------------------------------------------------------------->生成测试数据表:EmployeeIFNOTOBJECT_ID('[Employee]')
3、ISNULLDROPTABLE[Employee]GOCREATETABLE[Employee]([username]NVARCHAR(10))INSERT[Employee]SELECTN'张三'UNIONALLSELECTN'李四'UNIONALLSELECTN'王五'GO--SELECT*FROM[Employee]-->SQL查询如下:DECLARE@SVARCHAR(100)SELECT@S=ISNULL(@S+',','')+[username]FROM[Employee]Zuolo：上面的查询语句不能添加orderby进行排序，否则只能得到最后一条记录PRINT@S
4、--RESULT:--张三,李四,王五另外一个请教各位大大？如何将'01,02,03,04'转换为1列的结果集01020304/*拆分字符串**/--拆分单列+序号--方法一：利用数字辅助表ifobject_id('fn_SplitTSQL')isnotnulldropfunctionfn_SplitTSQLGOcreateFUNCTIONdbo.fn_SplitTSQL(@sNVARCHAR(MAX),@splitNCHAR(1))RETURNSTABLEASRETURNSELECTn-LEN(REPLACE(LEFT(array,n),@split,''))+1ASrn,SU
5、BSTRING(array,n,CHARINDEX(@split,array+@split,n)-n)AScolFROM(SELECT@sASarray)ASDJOINdbo.NumsONn<=LEN(array)ANDSUBSTRING(@split+array,n,1)=@split;GO--方法二：直接拆分ifobject_id('f_split')isnotnulldropfunctionf_splitgocreatefunctionf_split(@svarchar(8000),--待分拆的字符串@splitvarchar(10)--数据分隔符)returnstable
6、asreturn(selectRow_number()over(orderbyNumber)rn,substring(@s,number,charindex(@split,@s+@split,number)-number)ascolfrommaster..spt_valueswheretype='p'andnumber<=len(@s+'a')andcharindex(@split,@split+@s,number)=number)goselect*fromdbo.f_split('11,2,3',',')select*fromdbo.fn_SplitTSQL('11,2,3',
7、',')GO--拆分多列ifobject_id('fn_MutiSplitTSQL')isnotnulldropfunctionfn_MutiSplitTSQLGOcreateFUNCTIONdbo.fn_MutiSplitTSQL(@sNVARCHAR(MAX),@splitNCHAR(1),@Sub@splitNCHAR(1)=N',')RETURNSTABLEASRETURNselect*from(SELECTd.rn,'col'+cast(n-LEN(REPLACE(LE
