Convert Comma Separated Values To Rows And Vice Versa Using In-built And Custom SQL Code

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+.

 

 

Code

SELECT TRY_CAST(value AS INT) as [Value]
FROM STRING_SPLIT ('100,101,102,103,104,105', ',')

 

Code

SELECT TRY_CAST(value AS varchar(3)) as [Value]
FROM STRING_SPLIT ('A,B,C,D,E,F', ',')

Sometimes, we need to use a row query for various other operations and so, we can also use a simple SELECT query as:

 

Using XML without including in the Function.

 

 

Code

DECLARE
@InputParameter NVARCHAR(MAX) = 'test1,test2,test3,test4,test5'
, @delimiter varchar(1) = ','
DECLARE @xml AS XML = CAST(('<X>'+REPLACE(@InputParameter,@delimiter ,'</X><X>')+'</X>') AS XML)
SELECT C.value('.', 'varchar(20)') AS [value]
FROM @xml.nodes('X') as X(C)

Case 2 - Convert Rows to Comma sparate values 

 

This is the opposite of  Case 1 where we have converted the comma separated values/strings into rows

 

Here we will be converting Rows to CSV :

 

Code Below,

DECLARE @t table
(
Id int,
Name varchar(10)
)
INSERT INTO @t
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d'
SELECT ID,
stuff(
(
SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
),1,1,'')
FROM (SELECT DISTINCT ID FROM @t )

In the above code, we have taken temporary variable @t which has string values in the "Name" column and we will be using "STUFF" and "FOR XML PATH" to convert the row level "Name" column data into a single comma separated string.

 

Thank you!!