title: How to collate rows to a delimited string in SQL2008R2
body: <p>We are currently upgrading a current data import process we have written in C#.</p>
  
  <p>As part of the upgrade process, we need to check the results of the import process from the rewrite against the results of the old system.</p>
  
  <p>One of the changes we made was breaking comma-delimited lists into rows in another table. This will enable us to filter results using a simple join.</p>
  
  <p>This is the old schema:</p>
  
  <pre><code>FormNumber      MainCategories
  1               blue,green,red
  2               yellow,red,blue
  3               white
  </code></pre>
  
  <p>Which we normalized to:</p>
  
  <pre><code>FormNumber      AttributeId      Value
  1               1                blue
  1               1                green
  1               1                red
  2               1                yellow
  2               1                red
  2               1                blue
  3               1                white
  </code></pre>
  
  <p>Now, our next step is to confirm that the results from the two processes are the same. One of these checks is to compare the MainCategories field of the old process with the results from the normalized tables.</p>
  
  <p>This leads us, finally, to the question: How do I create a comma-delimited list of the new schema to compare to the value of the old.</p>
  
  <p>We have tried the XMLPath solution proposed by @Ritesh here: <a href="https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string">Concatenate many rows into a single text string?</a></p>
  
  <p>Here is the adapted sql statement:</p>
  
  <pre><code>Select distinct ST2.FormNumber, 
             (Select ST1.Value + ',' AS [text()]
              From cache.ArtifactAttribute ST1
              Where ST1.FormNumber= ST2.FormNumber
              ORDER BY ST1.FormNumber
              For XML PATH ('')) [Values]
       From cache.ArtifactAttribute ST2
  </code></pre>
  
  <p>The problem is the results are not correct. Even though FormNumber 1 only has three entries in the table, the Values column (the dynamically built delimited string) shows incorrect results. Obviously we are not implementing the sql code correctly.</p>
  
  <p>What are we doing wrong?</p>