title: alternative to stuff function in sql server
body: <p>i have table Tab1 like below( my actual table has upto 20,000 rows </p>
  
  <pre><code>a     b      c    statecode
  -------------------------------------------
  A1   abc   def    FL          
  B1   abc   def    AZ
  C1   abc   def    MI
  D1   abc   def    CO
  E1   abc   def    IL
  F1   abc   def    CA
  </code></pre>
  
  <p>Here is what i am trying to do: for a given combination of columns b and c if there exists multiple rows then i want to concatenate a comma delimited  "a - statecode".</p>
  
  <p>i need a result like below...</p>
  
  <pre><code>a     b      c    statecode   output
  -------------------------------------------
  A1   abc   def      FL          A1-FL,B1-AZ,C1-MI,D1-CO,E1-IL,F1-CA
  </code></pre>
  
  <p>What i tried:
  I tried sql stuff function for this</p>
  
  <pre><code>SELECT a, b,c,
    STUFF((SELECT  distinct ', '+ t1.a +'-'+ t1.statecode
               FROM Tab1 t1
               where  t2.b = t1.a
               and    t2.c= t1.c 
               FOR XML PATH('')), 1, 1,'') AS output
  from Tab1 t2;
  </code></pre>
  
  <p>This works fine for relatively small tables, my actual table has 20,000 rows and 15 columns, i have to check equality for a combination of 10 columns(like b,c in this example there are 8 more).</p>
  
  <p>Is there a better way to do this in sql?or improve performance of stuff function?</p>