A lot of times, we come across or face problems in converting rows into a CSV file and vice versa. So, in order to overcome this, there are different inbuilt and custom functions or SQL queries which return table-valued or scalar-valued result sets.
So, let's assume two cases where we need to combine the results and vice-versa.
Case 1: Convert CSV (Comma Separated Values/String ) to the table-valued output like below.
I have a dataset like this.
I need the output like this.

To accomplish this, we have different ways - either we could use a user-defined function or an in-built function, or a simple SELECT query.
So, let's start with the following to convert rows into CSV values using -
Conventional Code with Function

Code follows
- create FUNCTION [dbo].[fn_split](
- @delimited NVARCHAR(MAX),
- @delimiter NVARCHAR(100)
- ) RETURNS @table TABLE (id INT IDENTITY(1,1), [value] NVARCHAR(MAX))
- AS
- BEGIN
- DECLARE @xml XML
- SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
- INSERT INTO @table([value])
- SELECT r.value('.','Nvarchar(MAX)') as item
- FROM @xml.nodes('/t') as records(r)
- RETURN
- END
The output ( or the execution part) -

We can use string or numeric data types for this conversion of numeric data into CSV.

Convert String data into CSV.

Code is below:
- select * from dbo.[fn_split]('100,101,102,103,104,105',',')
- select * from dbo.[fn_split]('A,B,C,D,E,F',',')
We can also use the in-built string function to achieve this using SQL Server 2016+.





Join the conversation! Your thoughts help the community grow.