CODEWITHSUNDEEP
sqlsql-serverstringt-sqlconcatenation
ashleedawg
ashleedawg

Reputation: 21619

Combine table column into single string (without UDF)

I'm trying to combine a field from multiple records into a single string in SQL Server or T-SQL.

For example, I have: 

 ID   myString
 1    This
 2    is
 3    a
 4    test!

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

I can combine two records using LAG (see below), and I suspect there's a WHILE in the solution (maybe a variation of this RedGate post), but I'm not sure how to proceed.

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

...returns:

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

Unfortunately, I can't create UDF's on this server. Therefore, SE Data Explorer is a representative place for testing:

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

Thoughts? Thanks!

Upvotes: 3

Views: 64

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

Example

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

Returns

NewStr
This is a test!

Another option

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

Select @S

Returns

This is a test!

Upvotes: 3

Related Questions