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)
Ah… careful now. This type of splitter is just as slow as using a WHILE Loop. Please see the following article for proof of that.
LikeLiked by 1 person
Apologies… I forgot to post the URL.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
LikeLiked by 1 person
Thanks a lot Jeff,
I am working on your concern, will findout the difference between traditional splitting methodology and XML method.
Thanks for your finding.
Virendra
LikeLike
Hi Virendra,
Could you give me an idea about how to obtain the initial table? I mean I would like to put the result from you final table in a table like the initial one. Like a reverse of you script.
Thank you,
Tamara
LikeLike
Hi Tamara,
You use as Select column1+’,’+Column2+’,’+Column3…………… from Tablename
If anything else, please do write in details.
Thanking You,
Virendra
LikeLike
Hi Virendra, thanks for the fast reply 🙂
What I ment was like this: Let’s say I have a table like the final one /the result from your script. And I would like to gather in one line all the lines for Virendra Yaduvanshi lte’s say, to put all the softwares that Virendra Yaduvanshi knows in one line, splitted by comma.
‘E001′,‘Virendra Yaduvanshi’,‘MS-SQL Server,C,C++,C#,.Net’
How cand I do that in an elegant manner, without to much coding…
Thanks again for sharing your knowledge 🙂
Tamara
LikeLike
Oh yes,
suppose your output is stored in a table named FinalData, You may use below query,
Select distinct EmpID,EmpName, (select String + ‘,’ as [text()] from FinalData T1
where T1.EmpID=T2.EmpID order by String for xml path( ” )) from FinalData T2
Now , there is a trick, please identify to remove distinct keyword from here …. (: (:
anyway i will let you know it too…..
LikeLike
Thank you, Virendra!
LikeLike
Reblogged this on modyrefay.
LikeLike
can i use this for multiple columns values
LikeLike
please let me know if there is any solution or suggestion for this.
LikeLike