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.
Convert Comma Separated Values To Rows And Vice Versa Using Some In Build And Custom SQL Code In SQL SERVER 
I need the output like this.
 
Convert Comma Separated Values To Rows And Vice Versa Using Some In Build And Custom SQL Code In SQL SERVER
 
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
 
Convert Comma Separated Values To Rows And Vice Versa Using Some In Build And Custom SQL Code In SQL SERVER
Code follows
  1. create FUNCTION [dbo].[fn_split](  
  2. @delimited NVARCHAR(MAX),  
  3. @delimiter NVARCHAR(100)  
  4. RETURNS @table TABLE (id INT IDENTITY(1,1), [value] NVARCHAR(MAX))  
  5. AS  
  6. BEGIN  
  7. DECLARE @xml XML  
  8. SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'  
  9. INSERT INTO @table([value])  
  10. SELECT r.value('.','Nvarchar(MAX)'as item  
  11. FROM @xml.nodes('/t'as records(r)  
  12. RETURN  
  13. END  
The output ( or the execution part) -
 
 Convert Comma Separated Values To Rows And Vice Versa Using Some In Build And Custom SQL Code In SQL SERVER
 
We can use string or numeric data types for this conversion of numeric data into CSV.
 
Convert Comma Separated Values To Rows And Vice Versa Using Some In Build And Custom SQL Code In SQL SERVER
 
Convert String data into CSV.
 
Convert Comma Separated Values To Rows And Vice Versa Using Some In Build And Custom SQL Code In SQL SERVER
 
Code is below:
  1. select * from dbo.[fn_split]('100,101,102,103,104,105',',')  
  2. 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+.
 
Convert Comma Separated Values To Rows And Vice Versa Using Some In Build And Custom SQL Code In SQL SERVER
 
Code
  1. SELECT TRY_CAST(value AS INTas [Value]  
  2. FROM STRING_SPLIT ('100,101,102,103,104,105'',')  
Convert Comma Separated Values To Rows And Vice Versa Using Some In Build And Custom SQL Code In SQL SERVER
 
Code
  1. SELECT TRY_CAST(value AS varchar(3)) as [Value]  
  2. 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.
 
Convert Comma Separated Values To Rows And Vice Versa Using Some In Build And Custom SQL Code In SQL SERVER
 
Code
  1. DECLARE  
  2. @InputParameter NVARCHAR(MAX) = 'test1,test2,test3,test4,test5'  
  3. , @delimiter varchar(1) = ','  
  4. DECLARE @xml AS XML = CAST(('<X>'+REPLACE(@InputParameter,@delimiter ,'</X><X>')+'</X>'AS XML)  
  5. SELECT C.value('.''varchar(20)'AS [value]  
  6. 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 :

Convert Comma Separated Values To Rows And Vice Versa Using Some In Build And Custom SQL Code In SQL SERVER
 
Code Below,
  1. DECLARE @t table  
  2. (  
  3. Id int,  
  4. Name varchar(10)  
  5. )  
  6. INSERT INTO @t  
  7. SELECT 1,'a' UNION ALL  
  8. SELECT 1,'b' UNION ALL  
  9. SELECT 2,'c' UNION ALL  
  10. SELECT 2,'d'  
  11. SELECT ID,  
  12. stuff(  
  13. (  
  14. SELECT ','+ [NameFROM @t WHERE Id = t.Id FOR XML PATH('')  
  15. ),1,1,'')  
  16. 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!!