时间戳

首页 > 解决方案 > SQL Server - 使用 STRING_SPLIT 函数拆分多行？

sql - SQL Server - 使用 STRING_SPLIT 函数拆分多行？

问题描述

使用 SQL Server 2016。

我有下表

 [ID]    [Name List1]   [Name List2]        [Name List3]
 1       a1,a2,a3       a21,a22             a31,a32,a33,a34,a45
 2       b1,b2          b21,b22,b23,b24     b31
 3       etc....

[ID] 是唯一标识符列。

我需要将所有这些逗号分隔的字段拆分为单独的记录。

到目前为止我做了什么：

SELECT a.*,  b.[Name List1] FROM  [TABLE1] a LEFT JOIN
(SELECT DISTINCT [ID], value AS [Name List1]
FROM [TABLE1] CROSS APPLY STRING_SPLIT([Name List1], ',')
WHERE value IS NOT NULL AND rtrim(value) <> '') b ON a.[ID]=b.[ID]

此查询将根据第一列拆分记录，即 [Name List1] 但我需要对所有列（[Name List2] 和 [Name List3] 也是如此）执行此操作。

有没有一种优雅的方法可以用最少的编码来实现它？

所需结果应包括这些逗号分隔值的所有可能组合：

 [ID]    [Name List1]   [Name List2]        [Name List3]
 1       a1             a21                 a31
 2       a2             a21                 a31
 3       a3             a21                 a31
 4     etc... meaning all possible combination of column splits

标签： sqlsql-servertsql

解决方案

如果要获得所有可能的组合，请使用STRING_SPLIT()and 三个CROSS APPLY运算符：

输入：

CREATE TABLE #Data (
   ID int,
   [Name List1] varchar(100),
   [Name List2] varchar(100),
   [Name List3] varchar(100)
)
INSERT INTO #Data
   (ID, [Name List1], [Name List2], [Name List3])
VALUES   
   (1, 'a1,a2,a3', 'a21,a22',         'a31,a32,a33,a34,a45'),
   (2, 'b1,b2',    'b21,b22,b23,b24', 'b31')

T-SQL：

SELECT 
   d.ID, 
   s1.[value] AS [Name List1],
   s2.[value] AS [Name List2],
   s3.[value] AS [Name List3]
FROM #Data d
CROSS APPLY STRING_SPLIT(d.[Name List1], ',') s1
CROSS APPLY STRING_SPLIT(d.[Name List2], ',') s2
CROSS APPLY STRING_SPLIT(d.[Name List3], ',') s3

输出：

ID  Name List1  Name List2  Name List3
1   a1          a21         a31
1   a1          a21         a32
1   a1          a21         a33
1   a1          a21         a34
1   a1          a21         a45
1   a1          a22         a31
1   a1          a22         a32
1   a1          a22         a33
1   a1          a22         a34
1   a1          a22         a45
…

如果您想获取每个子字符串位置的所有可能组合，那么STRING_SPLIT()这里不是一个选项，因为此函数返回一个包含所有子字符串的表，但它们没有排序，并且不保证子字符串的顺序。在这种情况下，一种选择是将文本转换为有效JSON数组REPLACE()，然后使用OPENJSON()默认模式将该JSON数组检索为表，该表具有列key，value并且type（key列包含指定数组中元素的索引）。

T-SQL：

SELECT 
   d.ID, 
   j1.[key] + 1 AS [Key List1], j1.[value] AS [Name List1],
   j2.[key] + 1 AS [Key List2], j2.[value] AS [Name List2],
   j3.[key] + 1 AS [Key List3], j3.[value] AS [Name List3]
FROM #Data d
CROSS APPLY OPENJSON('["' + REPLACE(d.[Name List1], ',', '","') + '"]') j1
CROSS APPLY OPENJSON('["' + REPLACE(d.[Name List2], ',', '","') + '"]') j2
CROSS APPLY OPENJSON('["' + REPLACE(d.[Name List3], ',', '","') + '"]') j3

输出：

ID  Key List1   Name List1  Key List2   Name List2  Key List3   Name List3
1   1           a1          1           a21         1           a31
1   1           a1          1           a21         2           a32
1   1           a1          1           a21         3           a33
1   1           a1          1           a21         4           a34
1   1           a1          1           a21         5           a45
1   1           a1          2           a22         1           a31
1   1           a1          2           a22         2           a32
1   1           a1          2           a22         3           a33
1   1           a1          2           a22         4           a34
1   1           a1          2           a22         5           a45
…

推荐阅读