Display Comma Separated Value Row Wise in SQL Server

In this post, we will learn how to display comma separated value row wise in SQL Server

Example-1: Comma separated values of a variable to rows
In the first Example we will take a variable which holds some values with comma separator and we will display each value in a row.

Step 1: Replace comma with tag from comma separated value list to make it as XML
Step 2: Read each node from XML and display value in rows

DECLARE @xml XML, @s NVARCHAR(MAX)
SELECT  @s = N'a,b,c,d,e'
SET     @xml = N'<t>' + REPLACE(@s,',','</t><t>') + '</t>'
SELECT  t.value('.','varchar(5)') as [delimited items]
FROM    @xml.nodes('/t') as a(t)

OUTPUT

Example-2: Comma separated values of a column to rows
In this example, we have two columns Col1 & Col2 in a table and Col2 stores comma separated values and our requirement is to display each value of Col2 in row form against Col1

Step 1: Replace comma with tag from comma separated value list to make it as XML
Step 2: Reading each node from the XML
Step 3: Cross Apply to generated each node value as row.

Suggested article to understand Cross Apply: APPLY Operator in SQL Server

DECLARE @TAB TABLE (col1 varchar(10), col2 varchar(10))
INSERT INTO @TAB
SELECT 'A' as col1, '1,2,3' as col2
UNION
SELECT 'B' as col1, '4,5,6' as col2

SELECT col1, Tags.val.value('.', 'VARCHAR(MAX)') AS col2
FROM(
    SELECT  col1, CAST('<t>' + REPLACE(col2, ',', '</t><t>') + '</t>' AS XML) AS TAG
    FROM    @TAB
) T CROSS APPLY TAG.nodes('/t') as Tags(val)

OUTPUT

Now let's transform the above code into a table valued function to make it reusable and flexible.

CREATE FUNCTION Split(
    @delimited NVARCHAR(MAX),
    @delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
    DECLARE @xml XML
    SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
    INSERT INTO @t(val)
    SELECT  r.value('.','varchar(MAX)') as item
    FROM  @xml.nodes('/t') as records(r)
    RETURN
END

Now, In the above table valued function we have to just pass delimited string and delimiter and it will return you a table with two columns id (sequential number) and val (all delimited value).
Delimiter could be any : Comma, Colon, Semi-Colon etc.

Suggested article to understand split function in detail: Split Function in SQL

Now, let's execute the table valued function with different delimiters

SELECT * FROM dbo.Split(N'a,b,c', ',')
SELECT * FROM dbo.Split(N'p:q:r', ':')

OUTPUT