SQL Server-在插入列之前对组合数字字符串进行排序

Question

我有两个字符串列(数千行(，每个字符串中都有有序的数字(每个字符串中可以有0到10个数字(。示例：

+------------------+------------+
|  ColString1      | ColString2 | 
+------------------+------------+
| 1;3;5;12;        | 4;6'       |     
+------------------+------------+
| 1;5;10           | 2;26;      |     
+------------------+------------+
| 4;7;             | 3;         |     
+------------------+------------+

最终的结果是将这两列组合在一起，对中的数字进行排序按升序排列，然后将每个数字放入单独的列中(最小、第二小等(。例如Colstring1是1;3;5;12;并且ColString2是4;6;需要返回1;3;4;5;6;12;，然后我使用xml将其分配到列中。

除了对数字进行排序的步骤(即，当我组合字符串时，我得到1；3；5；12；4；6；即不按升序(外，使用xml进行Everthing操作很好
我试着先将它们放入JSON数组中进行排序，认为我可以执行top[1]等操作，但没有成功。关于如何组合2列并在插入列之前对其进行排序的任何帮助：

迄今为止的步骤：示例数据：

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, ColString1 VARCHAR(50), ColString2 VARCHAR(50));
INSERT INTO @tbl (ColString1, ColString2)
VALUES
('1;3;5;12;', '4;6;'),
('1;5;10;', '2;26;'),
('14;', '3;8;');

XML方法(组合字符串并放入列中，但顺序不正确(：

;WITH Split_Numbers (xmlname)
AS
(
SELECT 
CONVERT(XML,'<Names><name>'  
+ REPLACE ( LEFT(ColString1+ColString2,LEN(ColString1+ColString2) - 1),';', '</name><name>') + '</name></Names>') AS xmlname
FROM @tbl
)
SELECT    
xmlname.value('/Names[1]/name[1]','int') AS Number1,    
xmlname.value('/Names[1]/name[2]','int') AS Number2,
xmlname.value('/Names[1]/name[3]','int') AS Number3,
xmlname.value('/Names[1]/name[4]','int') AS Number4,
xmlname.value('/Names[1]/name[5]','int') AS Number5 
--etc for additional columns
FROM Split_Numbers

当前输出：数字顺序不正确，

+---------+---------+---------+---------+---------+
| Number1 | Number2 | Number3 | Number4 | Number5 |
+---------+---------+---------+---------+---------+
|       1 |       3 |       5 | 12      | 4       |
|       1 |       5 |      10 | 2       | 26      |
|      14 |       3 |       8 | NULL    | NULL    |
+---------+---------+---------+---------+---------+

所需输出：按升序排列的数字。

+---------+---------+---------+---------+---------+
| Number1 | Number2 | Number3 | Number4 | Number5 |
+---------+---------+---------+---------+---------+
|       1 |       3 |       4 | 5       | 6       |
|       1 |       2 |       5 | 10      | 26      |
|      3  |       8 |      14 | NULL    | NULL    |
+---------+---------+---------+---------+---------+

JSON方法：将列组合成一个JSON数组，但在使用JSON格式时，我仍然无法正确排序。

REPLACE ( CONCAT('[',   LEFT(ColString1+ColString2,LEN(ColString1+ColString2) - 1), ']') ,';',',')

无论是否有方法在输入之前对xml或JSON字符串进行排序，我们都将非常感谢您的帮助。如果有更简单的解决方案，很乐意考虑另一种方式。

Answer 1

您可以使用string_agg()和string_split():

select t.*, newstring
from t cross apply
(select string_agg(value, ',') order by (value) as newstring
from (select s1.value
from unnest(colstring1, ',') s1
union all
select s2.value
from unnest(colstring2, ',') s2
) s
) s;

也就是说，您可能应该将精力放在修复数据模型上。将数字存储在字符串中是不好的。在一个字符串中存储多个值很糟糕，很糟糕。如果这些数字是对其他表的外来引用，那就是坏，坏，坏。

Answer 2

在等待DDL和示例数据填充等时，这里有一个概念示例。它使用XQuery及其FLWOR表达式。

CTE承担了大部分繁重的工作：

• 将两列的值连接到一个字符串中。CONCAT()函数可防止NULL值
• 将其转换为XML数据类型
• 通过将XML元素的值转换为FLWOR表达式中的int数据类型来对其进行排序
• 过滤掉没有合法值的XML元素

其余的都很琐碎。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col1 VARCHAR(100), col2 VARCHAR(100));
INSERT INTO @tbl (col1, col2)
VALUES
('1;3;5;12;', '4;6;'),
('1;5;10;', '2;26;');
-- DDL and sample data population, end
DECLARE @separator CHAR(1) = ';';
;WITH rs AS
(
SELECT *
, CAST('<root><r><![CDATA[' + 
REPLACE(CONCAT(col1, col2), @separator, ']]></r><r><![CDATA[') + 
']]></r></root>' AS XML).query('<root>
{
for $x in /root/r[text()]
order by xs:int($x)
return $x
}
</root>') AS sortedXML
FROM @tbl
)
SELECT ID
, c.value('(r[1]/text())[1]','INT') AS Number1
, c.value('(r[2]/text())[1]','INT') AS Number2
, c.value('(r[3]/text())[1]','INT') AS Number3
-- continue with the rest of the columns
FROM rs CROSS APPLY sortedXML.nodes('/root') AS t(c);

输出

+----+---------+---------+---------+
| ID | Number1 | Number2 | Number3 |
+----+---------+---------+---------+
|  1 |       1 |       3 |       4 |
|  2 |       1 |       2 |       5 |
+----+---------+---------+---------+