title: T-SQL, Select @variable within a sub-query causes syntax error
body: <p>I'm using a sub-query to get results needed (multiple records returned), and I want to put those results in a single record returned.</p>
  
  <p>When I run the sub-query on its own, it works, but once I use it as a sub query, it no longer works due to a syntax error.</p>
  
  <p>The following code causes a syntax error </p>
  
  <blockquote>
    <p>(Incorrect syntax near '='.)</p>
  </blockquote>
  
  <pre><code>declare @test varchar(1000)
  set @test = ''  
  
  SELECT description, (SELECT @test = @test + FirstName
                  FROM EMP_tblEmployee
                  )select @test
  FROM EMP_tblCrew
  </code></pre>
  
  <p>So essentially, the sub query</p>
  
  <pre><code>(SELECT @test = @test + FirstName
                      FROM EMP_tblEmployee
                      )select @test
  </code></pre>
  
  <p>returns "charliejohnjacob"</p>
  
  <p>The main query</p>
  
  <pre><code>SELECT description FROM EMP_tblCrew
  </code></pre>
  
  <p>returns "janitor"</p>
  
  <p>So I want it to say</p>
  
  <blockquote>
    <p>janitor | charliejohnjacob</p>
  </blockquote>
  
  <p>2 fields, 1 record.</p>