3 回答

TA貢獻1788條經驗 獲得超4個贊
您可以使用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

TA貢獻2003條經驗 獲得超2個贊
檢查一下
SELECT A.OtherID, Split.a.value('.', 'VARCHAR(100)') AS Data FROM ( SELECT OtherID, CAST ('<M>' + REPLACE(Data, ',', '</M><M>') + '</M>' AS XML) AS Data FROM Table1 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a);
添加回答
舉報