谈到相关逗号分隔字符串到各行多列

Question

此前有人问起Turning a Comma Separated string into individual rows。这对于一列很适用，但我有一个扩展问题。如果我想分割两个（或更多）相关列，该怎么办？也就是说，如果我有这样的数据（使用数据从以前的文章为基础）：

| SomeID   | OtherID  | Data  | RelatedData | 
+----------------+-------------+-----------+-------------+ 
| abcdef-..... | cdef123-... | 18,20,22 | xxx,,yyy | 
| abcdef-..... | 4554a24-... | 17,19  | a,bb  | 
| 987654-..... | 12324a2-... | 13,19,20 | r,s,t  | 

而且想返回像行如下：

| SomeID   | OtherID  | Data | RelatedData | 
+----------------+-------------+------+-------------+ 
| abcdef-..... | cdef123-... | 18 | xxx   | 
| abcdef-..... | cdef123-... | 20 |    | 
| abcdef-..... | cdef123-... | 22 | yyy   | 
| abcdef-..... | 4554a24-... | 17 | a   | 
| abcdef-..... | 4554a24-... | 19 | bb   | 
| ...   | ...   | ... | ...   | 

我希望能使用STRING_SPLIT由于其简单，但我找不到一种方法使其工作。这是行不通的：

select OtherID, cs1.Value, cs2.Value 
from yourtable 
cross apply STRING_SPLIT (Data, ',') cs1 
cross apply STRING_SPLIT (RelatedData, ',') cs2 

有什么建议吗？

Answer 1

我同意关于这种设计的意见。将数据标准化会更好。

要使用string_split()做到这一点，你可以使用row_number()解决方法的顺序，像这样：

;with cs1 as (
    select SomeId, OtherId, x.Value 
    , ItemNumber = row_number() over (partition by t.SomeId, t.OtherId order by (Select Null)) 
    from t 
    cross apply string_split(Data,',') x 
) 
, cs2 as (
    select SomeId, OtherId, x.Value 
    , ItemNumber = row_number() over (partition by t.SomeId, t.OtherId order by (Select Null)) 
    from t 
    cross apply string_split(RelatedData,',') x 
) 
select cs1.SomeId, cs1.OtherId, cs1.Value, cs2.Value 
from cs1 
    inner join cs2 
    on cs1.SomeId = cs2.SomeId 
    and cs1.OtherId = cs2.OtherId 
    and cs1.ItemNumber = cs2.ItemNumber 

dbfiddle.uk demo

回报：

+--------------+-------------+-------+-------+ 
| SomeId | OtherId | Value | Value | 
+--------------+-------------+-------+-------+ 
| 987654-..... | 12324a2-... | 13 | r  | 
| 987654-..... | 12324a2-... | 19 | s  | 
| 987654-..... | 12324a2-... | 20 | t  | 
| abcdef-..... | 4554a24-... | 17 | a  | 
| abcdef-..... | 4554a24-... | 19 | bb | 
| abcdef-..... | cdef123-... | 18 | xxx | 
| abcdef-..... | cdef123-... | 20 |  | 
| abcdef-..... | cdef123-... | 22 | yyy | 
+--------------+-------------+-------+-------+ 

---

我认为这是简单使用包含ordi的自定义函数最终。例如，分隔的分裂内嵌表值函数由Jeff模式N：

select SomeId, OtherId, cs1.Item, cs2.Item 
from t 
    cross apply dbo.delimitedsplit8K(Data,',') cs1 
    cross apply dbo.delimitedsplit8K(RelatedData,',') cs2 
where cs1.ItemNumber = cs2.ItemNumber 

返回相同的结果。

---

分割字符串参考：

• Tally OH! An Improved SQL 8K “CSV Splitter” Function - Jeff Moden
• Splitting Strings : A Follow-Up - Aaron Bertrand
• Split strings the right way – or the next best way - Aaron Bertrand
• string_split() in SQL Server 2016 : Follow-Up #1 - Aaron Bertrand
• Ordinal workaround for **string_split()** - Solomon Rutzky