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 VirendraTestAS 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 VirendraTestAS Data CROSS APPLY String.nodes (‘/M’AS Split(Data)

 

 

Advertisements
Comments
  1. Jeff Moden says:

    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.

    Liked by 1 person

  2. 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

    Like

  3. 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

    Like

    • 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…..

      Like

  4. Tamara Cojocaru says:

    Thank you, Virendra!

    Like

  5. modyrefay says:

    Reblogged this on modyrefay.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s