--方法一:嵌套游标 --表名 字段名 --TableD AStr BStr CStr --TableM AStr BStr --TableResult AllStr --符合条件的数据放到表TheSameRecord Select TableD.AStr,TableD.BStr,TableD.CStr Into TheSameRecord From TableM,TableD Where TableM.AStr = TableD.AStr And TableM.BStr = TableD.BStr Declare @AStr VarChar(50) Declare @BStr VarChar(50) Declare @CStr VarChar(50) Declare @AllCStr VarChar(500) Set @AllCStr = '' Declare @iNext Bit Set @iNext = 0 --外层游标 Declare Outer_Cursor Cursor For Select Distinct AStr,BStr From TheSameRecord Open Outer_Cursor Fetch Next From Outer_Cursor Into @AStr, @BStr While @@Fetch_Status = 0 Begin --内层游标 Declare Inner_Cursor Cursor For Select CStr From TheSameRecord Where AStr = @AStr And BStr = @BStr Set @AllCStr = '' Open Inner_Cursor Fetch Next From Inner_Cursor Into @CStr While @@Fetch_Status = 0 Begin Set @AllCStr = @AllCStr + @CStr + ',' Fetch Next From Inner_Cursor Into @CStr End if (@iNext = 0) Begin Delete TableResult Set @iNext = 1 End Insert Into TableResult (AllStr) Values (SubString( @AllCStr,1, Len(@AllCStr)-1)) Print SubString( @AllCStr,1, Len(@AllCStr)-1 ) Close Inner_Cursor DealLocate Inner_Cursor Fetch Next From Outer_Cursor Into @AStr, @BStr End Close Outer_Cursor DealLocate Outer_Cursor --删除表TheSameRecord Drop Table TheSameRecord Select * From TableResult
那么就有如下实现了: --方法二:函数 Create Function AddStr(@AStr VarChar(50), @BStr VarChar(50)) Returns VarChar(8000) As Begin Declare @ALLStr Varchar(8000) Set @ALLStr='' Select @ALLStr = @ALLStr + ',' + Cast(CStr As VarChar) From TheSameRecord Where AStr = @AStr And BStr = @BStr Set @ALLStr = Right(@ALLStr,Len(@ALLStr)-1) Return(@ALLStr) End Go Select TableD.AStr,TableD.BStr,TableD.CStr Into TheSameRecord From TableM,TableD Where TableM.AStr = TableD.AStr And TableM.BStr = TableD.BStr Go Select Distinct AStr,BStr,dbo.AddStr(AStr,BStr) As AllStr From TheSameRecord Drop Table TheSameRecord