sql server 多行数据如何合并成一行。

[xuyiwei2018]

如图，想让 Topic 相同的行合并成一行

[全栈极简]

想达到什么样的效果，group by topic 行不行？

[xuyiwei2018]

Detail 要累加起来

[xuyiwei2018]

引用 楼主 xuyiwei2018 的回复:

如图，想让 Topic 相同的行合并成一行

Detail 要累加起来

[偶是菜鸟]

用sql 中的FOR XML PATH ，不会去百度下

[O西瓜]

declare @topic int set @topic=3 declare @detail nvarchar(2000) set @detail='' select @detail=@detail+Detail from tb where topic=@topic select @detail

[soaringbird]

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

[xuyiwei2018]

引用 6 楼 soaringbird 的回复:

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, '')
 )N

Detail是nvarchar(MAX)格式的

[O西瓜]

select distinct topic ,
Detail=(select  Detail+''  from tb b  where a.topic=b.topic  for xml path('') )
 from tb a

使用查询分析器看的话，过长会被自动截断，用程序获取不会有问题。

[jiaoshiyao]

Declare @Detail Nvarchar(MAX) SELECT COUNT(*),@Detail + Detail FROM [TAB] SELECT @Detail