合并相同项 求 最好贴上语句

合并列值原著：邹建改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳表结构，数据如下：id value----- ------1 aa1 bb2 aaa2 bbb2 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  ), '<N value="', ','), '"/>', ''), 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
------解决方案--------------------
没什么规律，不好合并。
------解决方案--------------------
SQL code
create table #test(id int,val nvarchar(20))insert into #test values(1,'门')insert into #test values(1,'men')insert into #test values(2,'们')insert into #test values(2,'men')insert into #test values(3,'大')insert into #test values(3,'da')insert into #test values(4,'打')insert into #test values(4,'da')with t as(select  min(a.val) as pinyin , max(a.val) as wenzi from #test a,#test b  where a.val=b.val group by a.id )select pinyin,( select wenzi+' ' from t awhere a.pinyin=b.pinyin for xml path('') ) as wenzi from t b group by pinyin
------解决方案--------------------
1、
先使用http://www.cnblogs.com/ret00100/archive/2010/08/06/1793725.html 下面的第二个函数求出每个汉字的拼音，然后再合并字符串（2楼的方法）
2、如果是按照6楼的格式的话，即第一行是汉字，下面一行是该汉字的拼音，那么按照6楼的即可