sql中的合并列值以及分拆列值

1.首先看合并列值：

/*表结构，数据如下： 
_id    _value 
----- ------ 
1    aa 
1    bb 
2    aaa 
2    bbb 
2    ccc 

需要得到结果： 
_id    _values 
------ ----------- 
1      aa,bb 
2      aaa,bbb,ccc 
即：group by id, 求 value 的和（字符串相加）*/

-------------------------------------------------------
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') 

--select * from tb

Create function dbo.f_str(@_id int)
returns varchar(8000)
as
begin
 declare @sql varchar(8000)
 set @sql = ''
 select @sql = @sql + ',' + cast(_value as varchar) from tb where _id = @_id
 return stuff(@sql,1,1,'')
end
--通过函数调用
select _id,dbo.f_str(_id) from tb group by _id

2.分拆列值：

/*1          aa,bb 
2          aaa,bbb,ccc 
欲按id,分拆value列, 分拆后结果如下: 
id          value 
----------- -------- 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc */

create table tb(id int,value varchar(30)) 
insert into tb values(1,'aa,bb') 
insert into tb values(2,'aaa,bbb,ccc') 

1. sql2000里的用法：
select a.id, substring(a.[value], b.number, charindex(',', a.[value] + ',', b.number) - b.number) 
FROM tb a, master..spt_values  b 
WHERE b.type='p' and substring(',' + a.[value],b.number, 1) = ',' 
2. 新的解决方法(sql server 2005) 

create table tb(id int,value varchar(30)) 
insert into tb values(1,'aa,bb') 
insert into tb values(2,'aaa,bbb,ccc') 
go 
SELECT a.id, b.value 
FROM( 
    SELECT id, [value] = CONVERT(xml,' <root> <v>' + REPLaCE([value], ',', ' </v> <v>') + ' </v> </root>') FROM tb 
)a 
OUTER aPPLY( 
    SELECT value = N.v.value('.', 'varchar(100)') FROM a.[value].nodes('/root/v') N(v) 
)b