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

SQL 语句一行拆成多行及多行合并成一行的方法一、SQL 语句对一行（单元格）数据拆分成多行有时候我们也许对一行数据拆分成多行的操作例如：Col1 COl2- -1 a,b,c2 d,e3 f拆分成：Col1 COl2- -1 a1 b1 c2 d2 e3 f下面给出几个经常用到的方法：1、SQL2000用辅助表ifobject_id(Tempdb.#Num)isnotnulldroptable#Numgoselecttop100ID=Identity(int,1,1)into#Numfromsyscolumnsa,syscolumnsbSelecta.Col1,COl2=substring(a.Col2,b.ID,charindex(,a.Col2+,b.ID)-b.ID)fromTaba,#Numbwherecharindex(,+a.Col2,b.ID)=b.ID-也可用substring(,+a.COl2,b.ID,1)=,2、SQL2005用Xmlselecta.COl1,b.Col2from(selectCol1,COl2=convert(xml,+replace(COl2,)+)fromTab)aouterapply(selectCol2=C.v.value(.,nvarchar(100)froma.COl2.nodes(/root/v)C(v)b3、用CTEwithroyas(selectCol1,COl2=cast(left(Col2,charindex(,Col2+,)-1)asnvarchar(100),Split=cast(stuff(COl2+,1,charindex(,Col2+,),)asnvarchar(100)fromTabunionallselectCol1,COl2=cast(left(Split,charindex(,Split)-1)asnvarchar(100),Split=cast(stuff(Split,1,charindex(,Split),)asnvarchar(100)fromRoywheresplit)selectCOl1,COl2fromroyorderbyCOl1option(MAXRECURSION0)二、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中只能用自定义的函数解决create table tb(id int, value varchar(10)insert into tb values(1, aa)insert into tb values(1, bb)insert into tb values(2, aaa)insert into tb values(2, bbb)insert into tb values(2, ccc)gocreate function dbo.f_str(id varchar(10) returns varchar(1000)asbegin declare str varchar(1000) select str = isnull(str + , , ) + cast(value as varchar) from tb where id = id return strendgo-调用函数select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_strdrop table tb-2、sql2005中的方法create table tb(id int, value varchar(10)insert into tb values(1, aa)insert into tb values(1, bb)insert into tb values(2, aaa)insert into tb values(2, bbb)insert into tb values(2, ccc)goselect id, value = stuff(select , + value from tb t where id = tb.id for xml path() , 1 , 1 , )from tbgroup by iddrop table tb-3、使用游标合并数据create table tb(id int, value varchar(10)insert into tb values(1, aa)insert into tb values(1, bb)insert into tb values(2, aaa)insert into tb values(2, bbb)insert into tb values(2, ccc)godeclare t table(id int,value varchar(100)-定义结果集表变量-定义游标并进行合并处理declare my_cursor cursor local forselect id , value from tbdeclare id_old int , id int , value varchar(10) , s varchar(100)open my_cursorfetch my_cursor into id , valueselect id_old = id , s=while FETCH_STATUS = 0begin if id = id_old select s = s + , + cast(value as varchar) else begin insert t values(id_old , stuff(s,1,1,) select s = , + cast(value as varchar) , id_old = id end