SQL SERVER – Split Comma Separated List Without Using a Function

The best way to learn something is to revisit some of the older blogs and then look at finding a more efficient way to work on the same problem. Recently I was looking at the forums to find a question come up again and again around making a comma separated list. This is not new over this blog and if you search you will get few. Long time ago I have written below blogs to show the values stored in table as comma separate list – this is not a rehash of what was written before but a newer way to solve the problem again.

SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP
SQL SERVER – Comma Separated Values (CSV) from Table Column

Recently someone asked me a reverse of that. They were storing comma separated values in a column of table. This table was getting populated from some source via SSIS package. Here is the simplified version of the problem I was presented.

DECLARE @t TABLE
(
EmployeeID INT,
Certs VARCHAR(8000)
)
INSERT @t VALUES (1,'B.E.,MCA, MCDBA, PGDCA'), (2,'M.Com.,B.Sc.'), (3,'M.Sc.,M.Tech.')

This is how data looks like in the table.

With the above data available, they want it split into individual values. Searching for possible solutions, here is the solution query I came up with:

SELECT EmployeeID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   @t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

Output looks like below.

Hope this would help you in your project. Let me know if you are able to find a different solution to the same problem. The best way to learn is to learn from each other.

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)