SDU Tools: Split a Delimited String (like a CSV) Into Columns Using T-SQL

I recently wrote about the tool we provide for splitting T-SQL delimited strings (like CSVs or comma-delimited strings). It outputs a row for every value.

Sometimes, however, I’m asked how I can get each value out into a separate column. Now that’s a little trickier without dynamic code because SQL Server wants to know the output metadata of the statement. SQL Server wants to know in advance what the output columns are called and what their data types are. This is especially important if you want to consume the output of the function in tools like SQL Server Integration Services (SSIS) or Biztalk.

However, we can go along way towards doing this by pre-defining the names of the output columns.

One of the tools in our free SDU Tools collection is SplitDelimitedStringIntoColumns.

This tool takes 3 parameters. Here is an example of it in use:

In this case, I passed the string to be split, the separator (a comma), and a flag to indicate whether I wanted the strings trimmed before being output. A single row is returned, and with 50 columns called Column01, Column02,… and so on up to Column50.

Here’s another example using a pipe as a separator: