一、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