資源描述：
《sql語(yǔ)句行數(shù)據(jù)拆成多行及多行數(shù)據(jù)合并成一行的方法》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在工程資料-天天文庫(kù)。
1、SQL語(yǔ)句一行拆成多行及多行合并成一行的方法一、SQL語(yǔ)句對(duì)一行（單元格）數(shù)據(jù)拆分成多行有時(shí)候我們也許對(duì)一行數(shù)據(jù)拆分成多行的操作例如：Col1????COl2---------------------1?????a,b,c2?????d,e3?????f拆分成：Col1????COl2-------------1?????a1?????b1?????c2?????d2?????e3?????f下面給出幾個(gè)經(jīng)常用到的方法：1、?SQL2000用輔助表if?object_id('Tempdb..#Num')?is?not?nulldrop?tabl
2、e?#Numgoselect?top?100?ID=Identity(int,1,1)?into?#Num?from?syscolumns?a,syscolumns?bSelect?a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID)?from?Tab?a,#Num?bwhere??charindex(',',','+a.Col2,b.ID)=b.ID?--也可用?substring(','+a.COl2,b.ID,1)=','2、SQL2005用Xmlse
3、lect?a.COl1,b.Col2from?(select?Col1,COl2=convert(xml,''+replace(COl2,',','')+'')?from?Tab)aouter?apply(select?Col2=C.v.value('.','nvarchar(100)')?from?a.COl2.nodes('/root/v')C(v))b3、用CTEwith?roy?as?(??select?Col1,COl2=cast(left(Col2,charindex(',',C
4、ol2+',')-1)?asnvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'')?asnvarchar(100))?from?Tabunion?allselect?Col1,COl2=cast(left(Split,charindex(',',Split)-1)?as?nvarchar(100)),??Split=cast(stuff(Split,1,charindex(',',Split),'')?asnvarchar(100))?from?Roy?whe
5、re?split>'')?select?COl1,COl2?from?roy?orderby?COl1?option?(MAXRECURSION?0)二、SQL語(yǔ)句SQL多行數(shù)據(jù)合并為一個(gè)單元格（行）描述：將如下形式的數(shù)據(jù)按id字段合并value字段。idvalue-----------1aa1bb2aaa2bbb2ccc需要得到結(jié)果：idvalue-----------------1aa,bb2aaa,bbb,ccc即：groupbyid,求value的和（字符串相加）*/--1、sql2000中只能用自定義的函數(shù)解決createtablet
6、b(idint,valuevarchar(10))insertintotbvalues(1,'aa')insertintotbvalues(1,'bb')insertintotbvalues(2,'aaa')insertintotbvalues(2,'bbb')insertintotbvalues(2,'ccc')gocreatefunctiondbo.f_str(@idvarchar(10))returnsvarchar(1000)asbegindeclare@strvarchar(1000)select@str=isnull(@str+',
7、','')+cast(valueasvarchar)fromtbwhereid=@idreturn@strendgo--調(diào)用函數(shù)selectid,value=dbo.f_str(id)fromtbgroupbyiddropfunctiondbo.f_strdroptabletb--2、sql2005中的方法createtabletb(idint,valuevarchar(10))insertintotbvalues(1,'aa')insertintotbvalues(1,'bb')insertintotbvalues(2,'aaa')inser
8、tintotbvalues(2,'bbb')insertintotbvalues(2,'ccc')goselectid,[value]=stuff((
