問題描述

我們目前正在升級我們用 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.

這篇關于如何在 SQL2008R2 中將行整理為分隔字符串的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！