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

1、SQL 語句一行拆成多行及多行合并成一行的方法一、SQL 語句對一行（單元格）數(shù)據(jù)拆分成多行有時候我們也許對一行數(shù)據(jù)拆分成多行的操作例如：Col1 COl2- -1 a,b,c2 d,e3 f拆分成：Col1 COl2- -1 a1 b1 c2 d2 e3 f下面給出幾個經(jīng)常用到的方法：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 b S

2、electa.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) from Tab a,#Num bwherecharindex(',',','+a.Col2,b.ID)=b.ID -也可用 substring(','+a.COl2,b.ID,1)=','2、SQL2005用Xmlselect a.COl1,b.Col2from(select Col1,COl2=convert(xml,'<root&

3、gt;<v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)aouter apply3、用CTEwith roy as(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) asnvarchar(100),Split=cast(stuff(COl2+',',1,charindex(',',Col

4、2+','),'') asnvarchar(100) from Tabunion allselect Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100),)select COl1,COl2 from roy orderby COl1 option (MAXRECURSION 0) Split=cast(stuff(Split,1,charindex(',',Split),'') asnvarchar(100) from Roy whe

5、re split>''二、SQL 語句SQL 多行數(shù)據(jù)合并為一個單元格（行） 描述：將如下形式的數(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 va

6、lues(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) asbegindeclare str varchar(1000)select str = isnull(str + ',' , '') + cast(value

7、as varchar) from tb where id = idreturn 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, &#

8、39;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)

9、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)并進行合并處理declare my_cursor cursor local forselect i

10、d , 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 = 0beginif id = id_oldselect s = s + ',' + cast(value as varchar) elsebegininsert t values(id_old , stuff(s,1,1,'')select s = ',' + cast(value as varchar) ,