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://blog.sqlauthority.com)
90 Comments. Leave new
Thank you Dave!
Even though the site said “… Without Using a Function” I really needed a proc. so, here it comes! Thank you again!
Jacob
CREATE PROCEDURE usp_Parse_Delimeted_Rows
(@TableName as varchar(100),
@Id_ColumnName as varchar(50),
@ColumnName as Varchar(50),
@Delimeter varchar(3))
AS
BEGIN
DECLARE @SQL_String As VARCHAR(1000)
SET @SQL_String = ‘SELECT ‘ + @Id_ColumnName +
‘ ,LTRIM(RTRIM(m.n.value(”.[1]”,” varchar(8000)”))) AS Parsed_Column
FROM
(
SELECT ‘ + @Id_ColumnName + ‘ ,CAST(”” + REPLACE(‘ + @ColumnName + ‘,”’ + @Delimeter + ”’,””) + ”” AS XML) AS x
FROM ‘ + @TableName + ‘
)t
CROSS APPLY x.nodes(”/XMLRoot/RowData”)m(n)’
EXEC(@SQL_String)
END
GO
If I understand what you need you can use the same code in sp. The Id column will give you what you need.
CREATE PROCEDURE usp_Parse_Delimeted_Rows
(@TableName as varchar(100),
@Id_ColumnName as varchar(50),
@ColumnName as Varchar(50),
@Delimeter varchar(3))
AS
BEGIN
DECLARE @SQL_String As VARCHAR(1000)
SET @SQL_String = ‘SELECT ‘ + @Id_ColumnName +
‘ ,LTRIM(RTRIM(m.n.value(”.[1]”,” varchar(8000)”))) AS Parsed_Column
FROM
(
SELECT ‘ + @Id_ColumnName + ‘ ,CAST(”” + REPLACE(‘ + @ColumnName + ‘,”’ + @Delimeter + ”’,””) + ”” AS XML) AS x
FROM ‘ + @TableName + ‘
)t
CROSS APPLY x.nodes(”/XMLRoot/RowData”)m(n)’
EXEC(@SQL_String)
END
GO
thank you so much for saving me time
This is how data looks like in the table.
CAN YOU PLEASE HELP ME , I WANT FOLLOWING OUTPUT OF QUERY ,
DEPT. ID NAME
1 SANDEEP,SAGAR
2 SANJAY,SUHAS
3 AJAY,AMAR
Hi Dave,
I am scenario like : in place of one field ,I have multiple fields where values are coming as comma separated and I wanted to the same way what your answered for One field .please can you suggest ,how we can achieve this in a single query?
ex:
id field 1 field 2 field 3
1 test,test1 test2,test3 tes43,test5
output:
id field 1 field 2 field 3
1 test tes2 test3
1 test1 test3 test5
Thanks in Advance,
Sitaram
typo :I have*