如何在 SQL2008R2 中将行整理为分隔字符串

2021-09-10 00:00:00 tsql sql-server

我们目前正在升级我们用 C# 编写的当前数据导入过程.

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.

这是旧模式:

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

我们标准化为:

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

现在，我们下一步是确认两个过程的结果是否相同.其中一项检查是将旧流程的 MainCategories 字段与规范化表的结果进行比较.

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.

我们在这里尝试了@Ritesh 提出的 XMLPath 解决方案:Concatenate多行合并成一个文本字符串?

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

这里是改编的sql语句:

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

问题是结果不正确.尽管 FormNumber 1 在表中只有三个条目，但 Values 列(动态构建的分隔字符串)显示了不正确的结果.很明显我们没有正确实现sql代码.

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.

我们做错了什么?

推荐答案

这里有一个方法供您尝试:

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

尽管存在问题，您无法真正确定连接的项目的顺序与您拥有的项目的顺序相同，因为没有一列明确给出该顺序.这是一个使用此示例的有效 SQL Fiddle.

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.

相关文章