title: How to fix this query to avoid syntax error in SQL Server 2008
body: <p>I would like to fix this query because SQL Server throws a syntax error.</p>
  
  <p>The purpose of the query is concatenate the column <code>Articulo.ReferenciaFichas</code> into one string to be included in a <code>select</code> statement.</p>
  
  <p><strong>Tables</strong></p>
  
  <p>Articulos:</p>
  
  <pre><code>        Codigo    Nombre           
          ---------------
          Z540021   Motul oil 1050w2
          Z540022   Motul oil 1030w
  </code></pre>
  
  <p>ReferenciaFichas:</p>
  
  <pre><code>       Codigo      Articulo
         --------------------           
         MF99.00102   Z540021
         MP08-0186    Z540021
         OUT1006      Z540021
         MTUL14       Z540022
  </code></pre>
  
  <p>To concatenate the column, I wrote this standalone query that works</p>
  
  <pre><code>DECLARE @provrefs VARCHAR(200)
  
  SELECT 
      @provrefs = COALESCE(@provrefs + ',', '') + [provlist].[Codigo]
  FROM
      (SELECT
           [Codigo]
       FROM 
           [GES16100].[dbo].[ReferenciasFichas]
       WHERE 
           Articulo = 'Z540021') provlist
  
  SELECT @provrefs
  </code></pre>
  
  <p>Result is:</p>
  
  <pre><code>MF99.00102,MP08-0186,OUT1006
  </code></pre>
  
  <p>But when I move this query to a select, the query not works due syntax error.
  I have tried moving the declare statement at the top of the file, and added
  extra parentheses. But I could not find any solution.</p>
  
  <p>How I can fix this ?</p>
  
  <p>This query is not working:</p>
  
  <pre><code>SELECT
      [arts].[Codigo] AS [Referencia],
      ((DECLARE @provrefs VARCHAR(200)            
        SELECT @provrefs = COALESCE(@provrefs + ',', '') + [provlist].[Codigo]
        FROM
            (SELECT [reff].[Codigo]
             FROM [GES16100].[dbo].[ReferenciasFichas] reff
             WHERE [reff].[Articulo] = [arts].[Codigo]) provlist)
  SELECT @provrefs) AS [Proveedores]
  FROM [GES16100].[dbo].[Articulos] AS arts
  </code></pre>
  
  <p>Expected results</p>
  
  <pre><code>Referencia    Proveedores
  --------------------------
  Z540021       MF99.00102,MP08-0186,OUT1006
  Z540022       MTUL14       
  </code></pre>