Convert comma separated string to table and vice versa

Convert table to comma separated string:

Create a table and populate it with some data :

--Create a table and populate it with some data

CREATE TABLE MyTable (MyCol VARCHAR(50))

INSERT INTO MyTable (MyCol) VALUES ('Yellow')

INSERT INTO MyTable (MyCol) VALUES ('Blue')

INSERT INTO MyTable (MyCol) VALUES ('Green')

INSERT INTO MyTable (MyCol) VALUES ('Red')

--Retrieve all records from the column in the table as a comma separated string into variable

DECLARE @CommaString VARCHAR(300)

SET @CommaString = ''

SELECT @CommaString = @CommaString + MyCol / cast (MyCol as varchar(max))  + ','

FROM MyTable

--trim trailing comma of string and return variable

SELECT LEFT(@CommaString, LEN(@CommaString) -1)

Convert comma separated string to table:

Unfortunately converting a comma separated string into a table isn’t as easy. Well, I mean it is fairly *easy* to do it, but there isn’t a nice and clean way of doing it. .net developers are blessed with the string method .Split() and yes creating a CLR to chop up these strings is definitely one good option to achieve this if that is what you decide to do. Other options to chop up a string to a table include:

- Use a loop to read/parse the string from left to right whilst extracting the values to a temp table

- Cast the string to xml by strategically replacing the comma’s to xml nodes and then using the nodes method to shred the xml to a table

- Use a CTE to break the string down into a temp table

- Use a tally table as shown here in an SSC article

Although I’ve always thought that doing the xml technique is probably consistently the quickest, I was quite intrigued by the tally table approach and I wondered if I could use the same method but eliminate the tally table thus saving those valuable reads. After pondering on the problem for a short while I recalled the Itzik CTE tally table and proceeded to try and use that instead of an actual tally table.

So here is my first attempt at doing this:

--declare a variable and populate it with a comma separated string

DECLARE @SQLString VARCHAR(MAX)

SET @SQLString = '111,211,311,411,5,6,7,811,911,1199,2299,3399,4499,5599,699,799,899,999';

--append a comma to the string to get correct results with empty strings or strings with a single value (no commas)

SET @SQLString = @SQLString + ',';

--the main query

--CTE to generate us a lot of numbers (this will produce up to 65536)

WITH Nbrs_3(n) AS (SELECT 1 UNION SELECT 0),

               Nbrs_2(n) AS (SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2),

               Nbrs_1(n) AS (SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2),

               Nbrs_0(n) AS (SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2),

               Nbrs  (n) AS (SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2)

--chop up the string based on the position of the comma returned from the inner query

SELECT SUBSTRING(@SQLString, n+1, CHARINDEX(',', @SQLString, n+1) - n-1)

FROM

(

          --select a holding record to ensure we get the very first value in the string

               SELECT 0 AS 'n'

               UNION ALL

          --select the maximum amount of generated numbers that we will need.

          --this will be the len of the string -1. the last char of string is a comma

               SELECT TOP(LEN(@SQLString)-1) ROW_NUMBER() OVER (ORDER BY n) AS 'n'

    FROM Nbrs

) x

--only return the numbers that equate to the position of a comma in the original string

--returning 0 ensures that we get the first value in the string.

WHERE SUBSTRING(@SQLString, n, 1) = ',' OR n = 0