SQL 語句行數(shù)據(jù)拆成多行及多行數(shù)據(jù)合并成一行的方法.docx

SQL 語句一行拆成多行及多行合并成一行的方法一、SQL 語句對(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用輔助表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 多行數(shù)據(jù)合并為一個(gè)單元格（行）描述：將如下形式的數(shù)據(jù)按id字段合并value字段。id value- -1 aa1 bb2 aaa2 bbb2 ccc需要得到結(jié)果：id value- -1 aa,bb2 aaa,bbb,ccc即：group by id, 求 value 的和（字符串相加）*/-1、sql2000中只能用自定義的函數(shù)解決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-調(diào)用函數(shù)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、使用游標(biāo)合并數(shù)據(jù)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)-定義結(jié)果集表變量-定義游標(biāo)并進(jìn)行合并處理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