
TSQL kommaseperierter String in eine Tabelle umwandeln
Sample table:
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‘
GO
The query
;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
Output
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