常规方法通常是建立下面的一个函数,缺点是没读一行数据,都要执行这个函数一遍
create
function
Gettxt(
@id
int
)
returns varchar ( 8000 )
as
begin
declare @s varchar ( 8000 )
set @s = ''
select @s = @s + ' , ' + txt from csdn where id = @id
-- return @s
return stuff ( @s , 1 , 1 , '' )
end
returns varchar ( 8000 )
as
begin
declare @s varchar ( 8000 )
set @s = ''
select @s = @s + ' , ' + txt from csdn where id = @id
-- return @s
return stuff ( @s , 1 , 1 , '' )
end
转成xml后替换(:
SELECT
*
FROM
(
SELECT
MonitID
FROM tbl_MediaMonit
)A
OUTER APPLY(
SELECT [ 电话 ] = STUFF ( REPLACE ( REPLACE (
(
SELECT MonitTel FROM tbl_MediaMonitTel N
WHERE uuMonitID = A.MonitID
FOR XML AUTO
), ' <N MonitTel=" ' , ' , ' ), ' "/> ' , '' ), 1 , 1 , '' )
)N
(
SELECT
MonitID
FROM tbl_MediaMonit
)A
OUTER APPLY(
SELECT [ 电话 ] = STUFF ( REPLACE ( REPLACE (
(
SELECT MonitTel FROM tbl_MediaMonitTel N
WHERE uuMonitID = A.MonitID
FOR XML AUTO
), ' <N MonitTel=" ' , ' , ' ), ' "/> ' , '' ), 1 , 1 , '' )
)N