Converting Delimited Columns Into Rows

Download Source Files: Here

I’ve seen a lot of post recently asking how can I search within a field of delimited values or join to a list of delimited values.  The truth is its very difficult.  The best approach is to turn that list of values from a rows into a columns.

In this post I’m going to show you a table valued udf that will do that for you.

First lets create a sample table

CREATE TABLE #CSV
(
   ID INT,
   Value VARCHAR(MAX)
);

INSERT INTO #CSV
VALUES
   (1,'1,2,3'),
   (2,'4,5'),
   (3,'6,7,8'),
   (4,'9,0')   

Now lets create the UDF that will transform the delimited column into rows.  The result set should look like this:

  

The UDF uses the XML and XQuery functionality of SQL Server to transpose the delimited columns into XML nodes then convert those nodes back into rows.

CREATE FUNCTION dbo.udf_ConvertCSV (@CSV AS NVARCHAR(MAX)) 
RETURNS @Result TABLE
(Field NVARCHAR(MAX)) 
AS
BEGIN

   DECLARE @XML XML
   
   SET @CSV = REPLACE(replace(@CSV,'&', '&amp;'),'<', '&lt;')
   SET @XML = '<Rows><Row><Field>' + 
               REPLACE(@CSV,',','</Field></Row><Row><Field>') 
               + '</Field></Row></Rows>'

   INSERT INTO @Result (Field)
   SELECT x.item.value('Field[1]','NVARCHAR(MAX)') AS Field
   FROM @XML.nodes('/Rows/Row')AS x(item)

RETURN
END;

Now lets see the UDF in action.

SELECT 
   #CSV.ID,
   Result.Field As Value
FROM 
   #CSV 
CROSS APPLY dbo.udf_ConvertCSV(#CSV.Value) AS Result

Your result set now should look something like this: