Apr 17, 2011

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

    Choose :
  • OR
  • To comment
No comments:
Write Comments

Probabilmente come studente universitario ti troverai a scrivere una o più tesi per il tuo analogo esame di fine carriera. Ma di cosa si tratta in particolare? Scopriamo quali sono i pro dei tool per riscrivere testo che troverai online. Per strumento di parafrasi si intende un tool online che si occupa di parafrasare in maniera immediata frasi oppure interi paragrafi. Questa attività si svolgerà interamente su internet e non lascerà alcuna traccia di plagio