title: Combine table column into single string (without UDF)
body: <p>I'm trying to combine a field from multiple records <strong>into a single string</strong> in SQL Server or T-SQL.</p>
  
  <blockquote>
    <p><strong><em>For example</strong>, I have:</em> </p>
  
  <pre><code> ID   myString
   1    This
   2    is
   3    a
   4    test!
  </code></pre>
    
    <p>...and <strong><em>I need</strong>:</em>  <code>This is a test!</code></p>
  </blockquote>
  
  <p><strong>I can combine two records</strong> using <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017" rel="nofollow noreferrer"><strong>LAG</strong></a> (see below), and I suspect there's a <a href="https://docs.microsoft.com/en-us/sql/t-sql/language-elements/while-transact-sql?view=sql-server-2017" rel="nofollow noreferrer"><strong>WHILE</strong></a> in the solution (maybe a variation of <a href="https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/" rel="nofollow noreferrer">this RedGate post</a>), but I'm not sure how to proceed.</p>
  
  <pre><code>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
  </code></pre>
  
  <p><em>...returns:</em></p>
  
  <pre><code>ID   myString   myTwoFields  
   1     This
   2     is        This is
   3     a         is a
   4     test!     a test!
  </code></pre>
  
  <p>Unfortunately, <strong>I can't create UDF's</strong> on this server.  Therefore, SE Data Explorer is a representative place for testing:</p>
  
  <blockquote>
    <p> See <a href="https://data.stackexchange.com/stackoverflow/query/847299/test-combine-column-into-single-string-without-udf" rel="nofollow noreferrer">this query</a> on SEDE, or you can <strong><a href="https://data.stackexchange.com/stackoverflow/query/fork/847299" rel="nofollow noreferrer">click here to fork it</a></strong> for testing.</p>
  </blockquote>
  
  <p><em>Thoughts?  Thanks!</em></p>