SQL 语句一行拆成多行及多行合并成一行的方法

一、SQL 语句对一行（单元格）数据拆分成多行    有时候我们也许对一行数据拆分成多行的操作 例如：  Col1       COl2 --------- ------------ 1          a,b,c 2           d,e 3           f 拆分成：  Col1       COl2 -------- ----- 1           a1           b 1           c 2           d 2           e 3           f  下面给出几个经常用到的方法：  1、 SQL2000用辅助表

  ifobject_id('Tempdb..#Num') is not null

 drop table #Num go

  select top 100ID=Identity(int,1,1) into #Num from

 syscolumnsa,syscolumns b Select

  a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-

b.ID)  from

 

Tab a,#Num b where

   charindex(',',','+a.Col2,b.ID)=b.ID --也可用substring(','+a.COl2,b.ID,1)=','        

 

  2、SQL2005用Xml

  select

 

a.COl1,b.Col2 from

   (selectCol1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+

'</v></root>') from

 Tab)a outer

 apply  (select Col2=C.v.value('.','nvarchar(100)')from a.COl2.nodes('/root/v'

)C(v))b    3、用CTE

  with roy as  (   select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1)asnvarchar(100)),   Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'')asnvarchar(100)) from Tab  union all  selectCol1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),   Split=cast(stuff(Split,1,charindex(',',Split),'') asnvarchar(100)) fromRoy where split>''  )    select COl1,COl2 from roy orderby COl1option (MAXRECURSION 0)     二、SQL 语句SQL 多行数据合并为一个单元格（行）  描述：将如下形式的数据按id字段合并value字段。 id    value ----- ------ 1     aa 1    bb 2     aaa 2     bbb 2    ccc 需要得到结果： id     value ------ ----------- 1      aa,bb

 

2      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') insertinto tb values(2, 'bbb') insert into tb values(2, 'ccc') go   create function dbo.f_str(@id varchar(10))returns varchar(1000) as begin    declare@str varchar(1000)    select @str = isnull(@str+ ',' , '') + cast(value as varchar) from tb where id = @id   return @str end go   --调用函数  select id , value = dbo.f_str(id) from tbgroup by id   drop function dbo.f_strdrop table tb    --2、sql2005中的方法  create tabletb(id int, value varchar(10)) insert into tb values(1, 'aa') insert into tbvalues(1, 'bb') insert into tb values(2, 'aaa') insert into tb values(2, 'bbb')insert into tb values(2, 'ccc') go  select id, [value] = stuff((select ',' + [value] from tb t where id =tb.id for xml path('')) , 1 , 1 , '') from tb group by id