Delimited Values into Rows
We have seen a lot of posts recently asking, how can I search within a field of delimited values or join to a list of delimited values. The truth is its very difficult. The best approach is to turn that list of values from a rows into a columns.
In this post we are going to show you a table valued udf that will do this transformation from delimted list, to rows.
The Solution
First, lets create a sample table.
You should have results that look like this.
Now lets create the UDF that will transform the delimited column into rows. The UDF uses the XML and XQuery functionality of SQL Server to transpose the delimited columns into XML nodes then convert those nodes back into rows.
Now lets see the UDF in action.
Your result set now should look something like this:
Downloads
Download Source Files: Here
