title: GROUP BY with concatenated values in select list
body: <p>I'm having a problem getting the following SELECT to run:</p>
  
  <pre><code>SET @final = '&lt;CoutUnitaire&gt;'
  
  SELECT  DISTINCT
      @final = COALESCE(@final +  '', ',') + 
      '&lt;row&gt;&lt;Intervention&gt;' + Code_Type_Mode + ' ' + Code_Complement + ' ' + Code_Phytocide + '&lt;/Intervention&gt;' + 
      '&lt;cout_moyen&gt;' + CAST(CAST(AVG(Travail.cout_par_ha) AS DECIMAL(10,2)) AS VARCHAR) + '&lt;/cout_moyen&gt;' +
      '&lt;/row&gt;' 
  FROM Travail 
      INNER JOIN Budget ON Travail.ID_Budget = Budget.ID_Budget 
      INNER JOIN Territoire ON Budget.ID_Territoire = Territoire.ID_Territoire 
      INNER JOIN Complement ON Travail.ID_Complement = Complement.ID_Complement 
      INNER JOIN Phytocide ON Travail.ID_Phytocide = Phytocide.ID_Phytocide 
      INNER JOIN Type_Mode ON Travail.ID_Type_Mode = Type_Mode.ID_Type_Mode 
      INNER JOIN #Years ON Budget.Annee_Budgetaire = #Years.intYear
  WHERE dbo.Budget.ID_Territoire IN (SELECT intTerritoryID FROM #Territories) AND (@circref = 0 OR circref = @circref)
  GROUP BY Code_Type_Mode + ' ' + Code_Complement + ' ' + Code_Phytocide
  ORDER BY Code_Type_Mode + ' ' + Code_Complement + ' ' + Code_Phytocide
  
  SET @final = @final + '&lt;/CoutUnitaire&gt;'
  </code></pre>
  
  <p>It returns:</p>
  
  <p>Column 'Type_Mode.Code_Type_Mode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  
  <p>Column 'Complement.Code_Complement' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  
  <p>Column 'Phytocide.Code_Phytocide' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  
  <hr>
  
  <p>Limitation:</p>
  
  <p>Running SQL Server 2000, so I don't have the option of populating this variable using FOR XML.   Need to build it manually.</p>
  
  <p>How do I go about getting these rows in the right order, while still concatenating them into a string?</p>