title: Group Concatenate in MS SQL Server
body: <p>I have two tables dbo.Supplier and dbo.L_ProductSupplier (AS LPST) and for all product IDs on LPST table, I want to concatenate the Supplier Names from dbo.Supplier (AS ST) which is linked to LPST by the SupplierID field (each product can have multiple suppliers) </p>
  
  <p>e.g. the query should take this:</p>
  
  <pre><code>LPST.ProductID  LPST.SupplierID ST.SupplierName
  1                          A    CompanyA
  1                          B    CompanyB
  1                          C    CompanyC
  2                          X    CompanyX
  2                          Y    CompanyY
  2                          Z    CompanyZ
  </code></pre>
  
  <p>And output this:</p>
  
  <pre><code>LPST.ProductID  SupplierName(s)
  1               CompanyA / CompanyB /CompanyC
  2               CompanyX / CompanyY / CompanyZ
  </code></pre>
  
  <p>I have tried the following code:</p>
  
  <pre><code>SELECT DISTINCT
  LPST.ProductId,
  STUFF((SELECT ',' + ST.SupplierName
  FROM dbo.Supplier AS ST
  WHERE LPST.SupplierId=ST.SupplierId
  FOR XML PATH('')),1,1,'') AS SupplierNames 
  FROM dbo.L_ProductSupplier AS LPST
  GROUP BY LPST.ProductId
  </code></pre>
  
  <p>...but it gives the error: Column 'dbo.L_ProductSupplier.SupplierId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Any ideas on how I can resolve this? If I remove the "Group By" then it reports each line on LPST without concatenation.</p>
  
  <p>Is there another way of doing it? I hear that cursors are an option but I'm new to SQL and aren't familiar with them; does anybody have experience with cursors in this instance?</p>