合并列值表结构，数据如下：    id value      ----- ------      1 aa      1 bb      2 aaa      2 bbb      2 ccc  需要得到结果：id values------ -----------1 aa,bb2 aaa,bbb,ccc即：group by id, 求 value 的和（字符串相加）1. 旧的解决方法(在sql server 2000中只能用函数解决。)--1. 创建处理函数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 int)RETURNS varchar(8000)ASBEGIN  DECLARE @r varchar(8000)  SET @r = ''  SELECT @r = @r + ',' + value FROM tb WHERE id=@id  RETURN STUFF(@r, 1, 1, '')ENDGO-- 调用函数SELECt id, value = dbo.f_str(id) FROM tb GROUP BY iddrop table tbdrop function dbo.f_str/*id value  ----------- -----------1 aa,bb2 aaa,bbb,ccc（所影响的行数为 2 行）*/--2、另外一种函数.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')go--创建一个合并的函数create function f_hb(@id int)returns varchar(8000)asbegin  declare @str varchar(8000)  set @str = ''  select @str = @str + ',' + cast(value as varchar) from tb where id = @id  set @str = right(@str , len(@str) - 1)  return(@str)Endgo--调用自定义函数得到结果：select distinct id ,dbo.f_hb(id) as value from tbdrop table tbdrop function dbo.f_hb/*id value  ----------- -----------1 aa,bb2 aaa,bbb,ccc（所影响的行数为 2 行）*/2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)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')go-- 查询处理SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(  SELECT [values]= STUFF(REPLACE(REPLACE(  (  SELECT value FROM tb N  WHERE id = A.id  FOR XML AUTO  ), '
     ', ''), 1, 1, ''))Ndrop table tb/*id values----------- -----------1 aa,bb2 aaa,bbb,ccc(2 行受影响)*/--SQL2005中的方法2create 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, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')from tbgroup by id/*id values----------- --------------------1 aa,bb2 aaa,bbb,ccc(2 row(s) affected)*/drop table tb 拆分列值有表tb, 如下:id value----------- -----------1 aa,bb2 aaa,bbb,ccc欲按id,分拆value列, 分拆后结果如下:id value----------- --------1 aa1 bb2 aaa2 bbb2 ccc1. 旧的解决方法(sql server 2000)SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)FROM tb A, # BWHERE SUBSTRING(',' + A.[values], B.id, 1) = ','DROP TABLE #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')goSELECT A.id, B.valueFROM(  SELECT id, [value] = CONVERT(xml,'
     
      
       ' + REPLACE([value], ',', '
      
      
       ') + '
      
     ') FROM tb)AOUTER APPLY(  SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v))BDROP TABLE tb/*id value----------- ------------------------------1 aa1 bb2 aaa2 bbb2 ccc(5 行受影响)*/

 http://www.wonima.com