title: SQL Server string concat issue for SQL Server 2000
body: <p>I was trying to concatenate values by comma when I will use group by. So I use <code>STUFF()</code> function and <code>for xml path('')</code> clause. Here is my script</p>
  
  <pre><code>IF EXISTS 
  ( 
   SELECT * 
   FROM tempdb.dbo.sysobjects 
   WHERE ID = OBJECT_ID(N'tempdb..#tmp1') 
  ) 
  BEGIN 
   DROP TABLE #tmp1 
  END 
  
  create table #tmp1
  (ID  varchar(2),CName varchar(20) )
  
  insert into #tmp1(ID,CName) values ('A','BBA Reman')
  insert into #tmp1(ID,CName) values ('B','BBA Reman')
  insert into #tmp1(ID,CName) values ('C','CT Tech')
  insert into #tmp1(ID,CName) values ('D','CT Tech')
  
  select
  t1.CName,
  stuff((
      select ',' + t.ID
      from #tmp1 t
      where t.CName = t1.CName
      order by t.ID
      for xml path('')
  ),1,1,'') as ConCatStr
  from #tmp1 t1
  group by t1.CName
  </code></pre>
  
  <p>The above script run successfully in SQL Server 2005 but not running in SQL Server 2000. So tell me how to restructure my script as a result it should run in SQL Server 2000 also.</p>