将逗号分隔的字符串变成单独的行 [英] Turning a Comma Separated string into individual rows

问题描述

我有一个这样的SQL表：

I have a SQL Table like this:

| SomeID         | OtherID     | Data
+----------------+-------------+-------------------
| abcdef-.....   | cdef123-... | 18,20,22
| abcdef-.....   | 4554a24-... | 17,19
| 987654-.....   | 12324a2-... | 13,19,20

是否存在查询，我可以在其中执行类似 SELECT OtherID，SplitData WHERE SomeID ='abcdef -.......'返回单个行，例如：

is there a query where I can perform a query like SELECT OtherID, SplitData WHERE SomeID = 'abcdef-.......' that returns individual rows, like this:

| OtherID     | SplitData
+-------------+-------------------
| cdef123-... | 18
| cdef123-... | 20
| cdef123-... | 22
| 4554a24-... | 17
| 4554a24-... | 19

基本将逗号处的数据分为几行？

Basically split my data at the comma into individual rows?

我知道将逗号分隔的字符串存储到关系数据库中听起来有些愚蠢，但是在消费者应用程序中的正常使用案例确实很有帮助。

I am aware that storing a comma-separated string into a relational database sounds dumb, but the normal use case in the consumer application makes that really helpful.

我不想在应用程序中进行拆分，因为我需要分页，所以我想在重构整个应用程序之前探索各种选择。

I don't want to do the split in the application as I need paging, so I wanted to explore options before refactoring the whole app.

这是 SQL Server 2008 （非R2）。

推荐答案

您可以使用SQL Server中出色的递归函数：

You can use the wonderful recursive functions from SQL Server:

示例表：

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
)

INSERT Testdata SELECT 1,  9, '18,20,22'
INSERT Testdata SELECT 2,  8, '17,19'
INSERT Testdata SELECT 3,  7, '13,19,20'
INSERT Testdata SELECT 4,  6, ''
INSERT Testdata SELECT 9, 11, '1,2,3,4'

---

查询

;WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)

SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

---

输出

 SomeID | OtherID | DataItem 
--------+---------+----------
 1      | 9       | 18       
 1      | 9       | 20       
 1      | 9       | 22       
 2      | 8       | 17       
 2      | 8       | 19       
 3      | 7       | 13       
 3      | 7       | 19       
 3      | 7       | 20       
 4      | 6       |          
 9      | 11      | 1        
 9      | 11      | 2        
 9      | 11      | 3        
 9      | 11      | 4        

这篇关于将逗号分隔的字符串变成单独的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！