Home » How to Split Comma Separated String Values into Columns

How to Split Comma Separated String Values into Columns

How to Split Comma Separated String Values into Columns?




Suppose you have a table, a column of table contains a comma separated string values and you are asked to split these comma separated string values into separate columns, means if string consists five sub string values which are separated by comma then you have to split this values into five columns based on their order.

Here we have a table named as EmpPhone as shown below.

As you can see, a table Emphone having column EmpPhoneNo which consists multiple phone number lists which are separated by commas, now we have to split these comma separated phone number into columns.

You can see maximum phone no that any employee can have that is four, so we will have maximum four phone number columns to split the comma separated phone number list into these columns as shown in below sample output that we want to achieve.

Columns phn1 , phn2, phn3, phn4  will have a phone number values.

Lets split the comma separated phone number list into columns, For this we will use Cross Apply operator, String_Split function and SQL pivot.

Following query is used for Splitting a comma separated phone number list into columns.

Select EmpID, EmpPhoneNo, ISNULL([phn1],'') AS [phn1], ISNULL([phn2],'') AS [phn2],
ISNULL([phn3],'') AS [phn3],
ISNULL([phn4],'') AS [phn4] from (
Select EmpId , EmpPhoneNo,
'Phn'+ CAST(Row_Number()Over(Partition By EmpId Order By EmpID) AS VARCHAR)AS Col,
Split.value 
From EmpPhone AS Emp
Cross apply String_split(EmpPhoneNo,',') as Split
) AS tbl
Pivot (Max(value) For Col IN ([phn1],[phn2],[phn3],[phn4])) AS Pvt

 

 





Cross Apply operator

String_Split

SQL pivot

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.