I have seen many Developer/DBA uses user defined function or stored procedure to split a comma separated ( or any delimiter to separate values like |,;-.& or any character/special character) column into rows using various coding logic like while/for loop or using cursor etc.
Let see an example, below is a sample data,
And requirement is to split the every SkillSet column values in a single row according EMPID and EMPNAME like
To get above desired result without using any Function, Stored Procedure or any loop, simple flat SQL select statement will be like this,
SELECT EmpID,EmpName,Split.Data.value(‘.’, ‘VARCHAR(100)’) AS String
FROM (Select EmpID,EmpName,CAST(‘<M>’ + REPLACE(SkillSet,‘,’, ‘</M><M>’)+ ‘</M>’ AS XML) AS String FROM VirendraTest) AS Data CROSS APPLY String.nodes (‘/M’) AS Split(Data)
If you want to do RnD, below is the sample code for same.
–Step 1 ) Create a Sample table named as VirendraTest
CreateTable VirendraTest(EmpID Char(5),EmpName Varchar(30),SkillSet nvarchar(100))
go
–Step 2 ) Insert few sample data in above created table i.e. VirendraTest
Insert into VirendraTest
values(‘E001’,‘Virendra Yaduvanshi’,‘MS-SQL Server,C,C++,C#,.Net’),
(‘E002’,‘Manish Raj’,‘Account,Cash Management,Admin’)
(‘E003’,‘Sanjay Singh’,‘.Net,VB6,Oracle’),
(‘E004’,‘Shajia Khan’,‘Coldfusion,Delphi’),
(‘E005’,‘Vikash Rai’,‘Php,Mongo,Cloud’),
(‘E006’,‘Sandeep Arora’,‘Network,C,C++’),
(‘E007’,‘Manpreet Kaur’,‘Java,Android,Mobile Devlopment’)
–Step 3 ) Check Table contents
Select * from VirendraTest
–Step 4 ) Split comma seperated column SKILLSET in rows using below query
SELECT EmpID,EmpName,Split.Data.value(‘.’, ‘VARCHAR(100)’) AS String
FROM (Select EmpID,EmpName,CAST(‘<M>’ + REPLACE(SkillSet,‘,’, ‘</M><M>’)+ ‘</M>’ AS XML) AS String FROM VirendraTest) AS Data CROSS APPLY String.nodes (‘/M’) AS Split(Data)
WikiDBA 