SQL 语句行数据拆成多行及多行数据合并成一行的方法.docxVIP

SQL 语句一行拆成多行及多行合并成一行的方法一、SQL 语句对一行（单元格）数据拆分成多行有时候我们也许对一行数据拆分成多行的操作例如：Col1 ? ? ? ?COl2--------- ------------1 ? ? ? ? ? a,b,c2 ? ? ? ? ? d,e3 ? ? ? ? ? f拆分成：Col1 ? ? ? ?COl2-------- -----1 ? ? ? ? ? a1 ? ? ? ? ? b1 ? ? ? ? ? c2 ? ? ? ? ? d2 ? ? ? ? ? e3 ? ? ? ? ? f下面给出几个经常用到的方法：1、?SQL2000用辅助表if?object_id(Tempdb..#Num)?is?not?nulldrop?table?#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用Xmlselect?a.COl1,b.Col2from?(select?Col1,COl2=convert(xml,rootv+replace(COl2,,,/vv)+/v/root)?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(,,Col2+,)-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?where?split)?select?COl1,COl2?from?roy?orderby?COl1?option?(MAXRECURSION?0)二、SQL 语句SQL 多行数据合并为一个单元格（行）描述：将如下形式的数据按id字段合并value字段。id value----- ------1 aa1 bb2 aaa2 bbb2 ccc需要得到结果：id value------ -----------1 aa,bb2 aaa,bbb,ccc即：group by id, 求 value 的和（字符串相加）*/--1、sql2000中只能用自定义的函数解决createtabletb(id int, value varchar(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+, , ) +cast(value asvarchar) fromtbwhere id =@idreturn@strendgo--调用函数select id , value =dbo.f_str(id) fromtbgroupby iddropfunctiondbo.f_strdroptabletb--2、sql2005中的方法createtabletb(id int, value varchar(10))insertintotbvalues(1, aa)insertintotbvalues(1, bb)insertintotbvalues(2, aaa)insertintotb