I have a SQL Table like this:
| SomeID | OtherID | Data
+----------------+-------------+-------------------
| abcdef-..... | cdef123-... | 18,20,22
| abcdef-..... | 4554a24-... | 17,19
| 987654-..... | 12324a2-... | 13,19,20
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.
It's SQL Server 2008
(non-R2).
Below works on sql server 2008
Will get all Cartesian product with the origin table columns plus "items" of split table.
Nice to see that it have been solved in the 2016 version, but for all of those that is not on that, here are two generalized and simplified versions of the methods above.
The XML-method is shorter, but of course requires the string to allow for the xml-trick (no 'bad' chars.)
XML-Method:
Recursive method:
Function in action
XML-METHOD 2: Unicode Friendly
Function
Use case
Or just a select with multiple result set
Please refer below TSQL. STRING_SPLIT function is available only under compatibility level 130 and above.
TSQL:
RESULT:
Colour
red blue green yellow black
I always use the XML method. Make sure you use VALID XML. I have two functions to convert between valid XML and Text. (I tend to strip out the carriage returns as I don't usually need them.
Check this