用事务实现函数效果

<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!-- [if !mso]> <object classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui> </object> <mce:style><!-- st1/:*{behavior:url(#ieooui) } -->

<!-- [endif]-->

--事務XML控制

<!-- [if gte mso 10]> <mce:style><!-- /* Style Definitions */ table.MsoNormalTable {mso-style-name:表格內文; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} -->

<!-- [endif]-->

-- 用事务/XML实现函数效果

create table ta( id int , name varchar ( 50))

insert ta

select 1, 'a' union all

select 1, 'b' union all

select 1, 'a' union all

select 2, 'e' union all

select 2, 'f' union all

select 3, 'g' union all

select 3, 'h' union all

select 3, 'i' union all

select 3, 'j' union all

select 4, 'k' union all

select 4, 'm' union all

select 4, 'l' union all

select 5, 'A' union all

select 5, 'B' union all

select 5, 'C' union all

select 5, 'D' union all

select 5, 'E' union all

select 6, 'F' union all

select 6, 'G' union all

select 6, 'H' union all

select 6, 'K' union all

select 6, 'M'

-- 合并效果

/*

ID Name

----------- ----------

1 a ,b,a

2 e,f

3 g ,h,i,j

4 k,m,l

5 A ,B,C,D,E

6 F ,G,H,K,M

(6 個資料列受到影響 )

*/

-- 方法 1( 不用函数实现更新、查询 ) 如下用于几列合并一列方法 1 比方法 2 效率高

declare @tb table ( id int , name varchar ( 50), con int identity ( 1, 1))

insert @tb

select * from ta

begin tran

while exists( select 1 from @tb)

begin

update a

set a. name= a. name+ ',' + b. name

from ta a , @tb b

where a. id= b. id and

not exists( select * from @tb where id= b. id and con< b. con )

delete b

from @tb b where not exists( select 1 from @tb where id= b. id and con< b. con)

end

select distinct id, 显示 = stuff ( name , 1, charindex ( ',' , name ), '' ) from ta

-- 以下用于更新 commit tran 替换 rollback tran

--update ta set name=stuff(name,1,charindex(',',name),'')

--commit tran

rollback tran

select * from ta

-- 方法 2( 通过创建函数实现查询更新、查询 )

create function ta_fun( @id int )

returns varchar ( 1000)

as

begin

declare @sql varchar ( 1000)

set @sql= ''

select @sql= @sql+ ',' + name from ta where id= @id

--print @sql

return stuff ( @sql, 1, 1, '' )

end

select distinct id, 显示 = dbo. ta_fun( id) from ta

--select id, 显示 =dbo.ta_fun(id) from ta group by id

-- 以下用函数更新

update ta set name = dbo. ta_fun( id)

--drop table ta 删测试表

--drop function ta_fun 删测试函数

-- 以下用 SQL2005 实现方法

--XML 方法 1

SELECT *

FROM ( SELECT DISTINCT id FROM ta ) A

OUTER APPLY

( SELECT [name]= STUFF ( REPLACE ( REPLACE (( SELECT name FROM ta N WHERE id = A. id FOR XML AUTO ), '<N name="' , ',' ), '"/>' , '' ), 1, 1, '' )) N

--XML 方法 2

select

ID, [Name]= stuff (( select ',' + Name from Ta where ID= a. ID for xml path ( '' )), 1, 1, '' )

from Ta a

group by ID