如何在sql server 2005中获取此输出，如何将多行中存在的多个字符串合并为单个字符串 [英] how to get this output in sql server 2005 ,How to merge multiple strings present in multiple rows as a single string

问题描述

获得的结果：

Result Obtained:

N0    Name
1       a
1       b
1       c
2       d

预期结果：

Expected Result :

N0  Name
1   abc
2   d

推荐答案

检查这个..

Check this..

Create Table #Temp(No int, Name Nvarchar(40))
Insert into #Temp
Select 1,'a' Union all
Select 1,'b' Union all
Select 1,'c' Union all
Select 2,'d'

Select Distinct t.No, 
STUFF((Select Distinct a.Name From #Temp a 
       Where a.No=t.No 
       for Xml Path(''),TYPE).value('.','VARCHAR(max)'), 1, 0, '') as CombinedList 
From #Temp t

Drop Table #Temp

输出：

Output:

No	CombinedList
---     ------------
1	abc
2	d

使用一个递归的Cte ...

Using a Recursive Cte...

Create Table #Temp(No int, Name Nvarchar(40))

Insert into #Temp 
Select 1,'a' Union all
Select 1,'b' Union all
Select 1,'c' Union all
Select 2,'d'

;With cte (No,Name,RankNo) as
(
Select No,Name,ROW_NUMBER() Over(Partition by No Order by Name) from #Temp 
)

,recurs_cte (No,CombinedName,RankNo) AS
(
SELECT Distinct No,Cast(Name as Nvarchar(Max)),RankNo FROM cte WHERE RankNo=1
UNION ALL
SELECT c.No,Cast(r.CombinedName + c.Name as Nvarchar(Max)),c.RankNo FROM recurs_cte r
INNER JOIN cte c ON r.No=c.No 
Where c.RankNo=r.RankNo+1
)

Select No,CombinedName From recurs_cte r 
Where RankNo in (Select Max(RankNo) from recurs_cte where No=r.No) Order by No

Drop Table #Temp

这篇关于如何在sql server 2005中获取此输出，如何将多行中存在的多个字符串合并为单个字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！