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!!