sql server - How to collate rows to a delimited string in SQL2008R2

Question

We are currently upgrading a current data import process we have written in C#.

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.

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.

This is the old schema:

FormNumber      MainCategories
1               blue,green,red
2               yellow,red,blue
3               white

Which we normalized to:

FormNumber      AttributeId      Value
1               1                blue
1               1                green
1               1                red
2               1                yellow
2               1                red
2               1                blue
3               1                white

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.

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.

We have tried the XMLPath solution proposed by @Ritesh here: Concatenate many rows into a single text string?

Here is the adapted sql statement:

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

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.

What are we doing wrong?

Answer 1

Here is a way for you to try:

SELECT DISTINCT A.FormNumber, MainCategories
FROM YourTable A
CROSS APPLY (SELECT STUFF((SELECT ',' + Value 
                           FROM YourTable
                           WHERE FormNumber = A.FormNumber FOR XML PATH('')),1,1,'') MainCategories) B

Though there is the problem where you can't really be sure that the order of the items concatenated is the same as the one you have, since there isn't a column that explictly gives that order. Here is a working SQL Fiddle with this example.