sqlserver×Ö·û´®ºÏ²¢(merge)·½·¨»ã×Ü

ÎÞÂÛÊÇÔÚsql 2000£¬»¹ÊÇÔÚ sql 2005 ÖÐ,¶¼Ã»ÓÐÌṩ×Ö·û´®µÄ¾ÛºÏº¯Êý£¬ËùÒÔ£¬µ±ÎÒÃÇÔÚ´¦ÀíÏÂÁÐÒªÇóʱ£¬»á±È½ÏÂé·³£ºÓбítb, ÈçÏ£ºid value----- ------1 aa1 bb2 aaa2 bbb2 cccÐèÒªµÃµ½½á¹û£ºid values------ -----------1 aa,bb2 aaa,bbb,ccc¼´£¬ group by id, Çó value µÄºÍ£¨×Ö·û´®Ïà¼Ó£© 1. ¾ÉµÄ½â¾ö·½·¨-- 1. ´´½¨´¦Àíº¯ÊýCREATE FUNCTION dbo.f_str(@id int)RETURNS varchar(8000)ASBEGINDECLARE @r varchar(8000)SET @r = ''SELECT @r = @r + ',' + valuefrom tbWHERE id=@idRETURN STUFF(@r, 1, 1, '')ENDGO-- µ÷Óú¯Êý SELECt id, values=dbo.f_str(id)from tbGROUP BY id -- 2.1 еĽâ¾ö·½·¨-- ʾÀýÊý¾ÝDECLARE @t TABLE(id int, value varchar(10))INSERT @t SELECT 1, 'aa'UNION ALL SELECT 1, 'bb'UNION ALL SELECT 2, 'aaa'UNION ALL SELECT 2, 'bbb'UNION ALL SELECT 2, 'ccc' -- ²éѯ´¦ÀíSELECT *from(SELECT DISTINCTidfrom @t)AOUTER APPLY(SELECT[values]= STUFF(REPLACE(REPLACE((SELECT value from @t NWHERE id = A.idFOR XML AUTO), '', ''), 1, 1, ''))N /*--½á¹ûid values----------- ----------------1 aa,bb2 aaa,bbb,ccc(2 ÐÐÊÜÓ°Ïì)--*/--2.2DECLARE @TB TABLE([Name] VARCHAR(1), [Value] VARCHAR(6))INSERT @TBSELECT 'A', '123' UNION ALLSELECT 'A', '677' UNION ALLSELECT 'B', 'HHDA' UNION ALLSELECT 'B', 'JYUKY' UNION ALLSELECT 'B', 'WRWFCW' UNION ALLSELECT 'B', 'YUYUY' UNION ALLSELECT 'C', 'TRREER' SELECT [Name],STUFF((SELECT ','+[Value] from @TB WHERE NAME=A.NAME FOR XML PATH('')),1,1,'') AS [Value]from @TB AS AGROUP BY [Name]/*Name Value---- ------------------------------------------A 123,677B HHDA,JYUKY,WRWFCW,YUYUYC TRREER*/ --¸÷ÖÖ×Ö·û´®·Öº¯Êý --3.3.1 ʹÓÃÓα귨½øÐÐ×Ö·û´®ºÏ²¢´¦ÀíµÄʾÀý¡£--´¦ÀíµÄÊý¾ÝCREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3 --ºÏ²¢´¦Àí--¶¨Òå½á¹û¼¯±í±äÁ¿DECLARE @t TABLE(col1 varchar(10),col2 varchar(100)) --¶¨ÒåÓα겢½øÐкϲ¢´¦ÀíDECLARE tb CURSOR LOCALFORSELECT col1,col2 from tb ORDER BY col1,col2DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)OPEN tbFETCH tb INTO @col1,@col2SE