使用XML帮助我用逗号分隔行。对于额外的逗号，我们可以使用SQL Server的replace函数。使用AS“data（）”将用空格连接行，而不是添加逗号，稍后可以用逗号替换，语法如下所示。

REPLACE(
        (select FName AS 'data()'  from NameList  for xml path(''))
         , ' ', ', ') 

   declare @phone varchar(max)='' 
   select @phone=@phone + mobileno +',' from  members
   select @phone

提出了递归CTE解决方案，但没有提供代码。下面的代码是递归CTE的示例。

请注意，虽然结果与问题相符，但数据与给定的描述并不完全相符，因为我假设您确实希望对行组（而不是表中的所有行）执行此操作。将其更改为与表中的所有行相匹配是读者的练习。

;WITH basetable AS (
    SELECT
        id,
        CAST(name AS VARCHAR(MAX)) name,
        ROW_NUMBER() OVER (Partition BY id ORDER BY seq) rw,
        COUNT(*) OVER (Partition BY id) recs
    FROM (VALUES
        (1, 'Johnny', 1),
        (1, 'M', 2),
        (2, 'Bill', 1),
        (2, 'S.', 4),
        (2, 'Preston', 5),
        (2, 'Esq.', 6),
        (3, 'Ted', 1),
        (3, 'Theodore', 2),
        (3, 'Logan', 3),
        (4, 'Peter', 1),
        (4, 'Paul', 2),
        (4, 'Mary', 3)
    ) g (id, name, seq)
),
rCTE AS (
    SELECT recs, id, name, rw
    FROM basetable
    WHERE rw = 1

    UNION ALL

    SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw + 1
    FROM basetable b
    INNER JOIN rCTE r ON b.id = r.id AND b.rw = r.rw + 1
)
SELECT name
FROM rCTE
WHERE recs = rw AND ID=4
OPTION (MAXRECURSION 101)

在SQL Server 2017或更高版本中，可以使用STRING_AGG（）函数生成逗号分隔的值。请看下面的一个示例。

SELECT
    VendorId, STRING_AGG(FirstName,',') UsersName
FROM Users
WHERE VendorId != 9
GROUP BY VendorId

我通常使用这样的select连接SQL Server中的字符串：

with lines as 
( 
  select 
    row_number() over(order by id) id, -- id is a line id
    line -- line of text.
  from
    source -- line source
), 
result_lines as 
( 
  select 
    id, 
    cast(line as nvarchar(max)) line 
  from 
    lines 
  where 
    id = 1 
  union all 
  select 
    l.id, 
    cast(r.line + N', ' + l.line as nvarchar(max))
  from 
    lines l 
    inner join 
    result_lines r 
    on 
      l.id = r.id + 1 
) 
select top 1 
  line
from
  result_lines
order by
  id desc

这也很有用

create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,'Peter'), (1,'Paul'), (1,'Mary'), (2,'Alex'), (3,'Jack')

DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + ',' + name, name) FROM #test WHERE id = 1
select @t
drop table #test

回报

Peter,Paul,Mary