sql - 将表列合并为单个字符串(无 UDF)

Question

我正在尝试在 SQL Server 或 T-SQL 中将来自多个记录的字段合并为一个字符串。

For example, I have:

 ID   myString
 1    This
 2    is
 3    a
 4    test!

...and I need: This is a test!

我可以使用 LAG 合并两条记录 (见下文)，我怀疑有一个 WHILE在解决方案中(可能是 this RedGate post 的变体)，但我不确定如何继续。

create table #temp (id int, myString varchar(max));
  insert into #temp values (1,'This');
  insert into #temp values (2,'is');
  insert into #temp values (3,'a');
  insert into #temp values (4,'test!');

select 
  myString,
  LAG(myString) OVER (ORDER BY id) + ' ' + myString as [myTwoFields]
from #temp

...返回:

ID   myString   myTwoFields  
 1     This
 2     is        This is
 3     a         is a
 4     test!     a test!

很遗憾，我无法在此服务器上创建 UDF。因此，SE Data Explorer 是具有代表性的测试场所:

🌟 See this query on SEDE, or you can click here to fork it for testing.

想法？谢谢!

Answer 1

示例

Select NewStr = Stuff((Select ' ' +MyString From #temp Order by ID For XML Path ('')),1,1,'') 

返回

NewStr
This is a test!

另一种选择

Declare @S varchar(max) = ''
Select @S = ltrim(@S+' '+MyString)
 From  #Temp
 Order by ID

Select @S

返回

This is a test!