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