declare @topic int set @topic=3 declare @detail nvarchar(2000) set @detail='' select @detail=@detail+Detail from tb where topic=@topic select @detail
select * from (select distinct topic from tableName o) A outer apply( select [Details]=STUFF(REPLACE(REPLACE( (select Detail from tableName o1 where topic=A.topic for xml auto), '<o1 Detail="', ','), '"/>', ''), 1, 1, '') )N
可以匹配到,可是当我把结果加到另一个表的时候,老是会截断,大概超过50KB的文本后面的就加不进来了. INSERT INTO Table_N0 (topic,Detail) select * from (select distinct topic from Posts0to1000 o) A outer apply( select [Details]=STUFF(REPLACE(REPLACE( (select Detail from Posts0to1000 o1 where topic=A.topic for xml auto), '<o1 Detail="', ','), '"/>', ''), 1, 1, '') )NDetail是nvarchar(MAX)格式的
select distinct topic , Detail=(select Detail+'' from tb b where a.topic=b.topic for xml path('') ) from tb a 使用查询分析器看的话,过长会被自动截断,用程序获取不会有问题。
declare @detail nvarchar(2000) set @detail=''
select @detail=@detail+Detail from tb where topic=@topic
select @detail
outer apply(
select [Details]=STUFF(REPLACE(REPLACE( (select Detail from tableName o1 where topic=A.topic for xml auto), '<o1 Detail="', ','), '"/>', ''), 1, 1, '')
)N
可以匹配到,可是当我把结果加到另一个表的时候,老是会截断,大概超过50KB的文本后面的就加不进来了.
INSERT INTO Table_N0
(topic,Detail)
select * from (select distinct topic from Posts0to1000 o) A
outer apply(
select [Details]=STUFF(REPLACE(REPLACE( (select Detail from Posts0to1000 o1 where topic=A.topic for xml auto), '<o1 Detail="', ','), '"/>', ''), 1, 1, '')
)NDetail是nvarchar(MAX)格式的
select distinct topic ,
Detail=(select Detail+'' from tb b where a.topic=b.topic for xml path('') )
from tb a
使用查询分析器看的话,过长会被自动截断,用程序获取不会有问题。
SELECT COUNT(*),@Detail + Detail FROM [TAB]
SELECT @Detail