How to Split Comma Separated String Values into Columns

How to Split Comma Separated String Values into Columns?




Suppose you have a table, and one column of this 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.

Lets create a sample table named PhoneNumberList and insert some records into this table as shown below.

CREATE TABLE dbo.PhoneNumberList
(EmpId INT IDENTITY(1,1),
EmpPhoneNo VARCHAR(100)
)

INSERT INTO dbo.PhoneNumberList
(EmpPhoneNo)
VALUES
(‘7898798797,8989893333,1212121213,4545454545’),
(‘1313131345,4567676767’),
(‘4746464646,9898989898,8900000000,3434343434’),
(‘7878787878’)

As you can see below sample table named  EmpPhone .

Now the requirement is that you have to split these comma separated phone number into columns.

You can see the maximum phone number that any employee is having that is four, so you will have maximum four columns that is used to split the comma separated phone number list into these columns as shown in below sample output that you need to implement.

Columns phn1, phn2, phn3, phn4 will be having 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 dbo.PhoneNumberList AS Emp 
 CROSS APPLY String_split(EmpPhoneNo,',') AS Split ) 
 AS tbl
Pivot (Max(Value) FOR Col IN ([phn1],[phn2],[phn3],[phn4])
) AS Pvt

As you can see, now the comma separated phone number string is split into separated columns.




Cross Apply operator

String_Split

SQL pivot

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 390 total views,  3 views today

Leave a Reply

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