Convert Semicolon Separated String in Rows Along With Respective Data

I have a situation, have a data in table with Primary key and one data field (assume it is a company) and third field contain the information of Person working in it in semicolon/comma separated format.

CREATE TABLE SemicolonSep

(

ID INT,

Company Nvarchar(20) ,

person VARCHAR(100)

)

INSERT SemicolonSep SELECT 1, 'A', 'Person1;Person2;Person3'

INSERT SemicolonSep SELECT 2, 'B', 'Person1'

INSERT SemicolonSep SELECT 3, 'C', ''

INSERT SemicolonSep SELECT 4, 'D', 'Person1;Person2;Person5;Person7'

INSERT SemicolonSep SELECT 5, 'E', 'Person1;Person2;Person5;Person7;Person15;Person17'

select * from SemicolonSep

;WITH temp(id, Company, NewData, person) AS

(

SELECT

id,

Company,

cast(LEFT(person, CHARINDEX(';', person + ';') - 1) as nvarchar),

STUFF(person, 1, CHARINDEX(';', person + ';'), '')

FROM SemicolonSep

UNION all

SELECT

id,

Company,

cast(LEFT(Person, CHARINDEX(';', Person + ';') - 1) as nvarchar),

STUFF(Person, 1, CHARINDEX(';', Person + ';'), '')

FROM temp

where Person > ''

)

SELECT

id,

Company,

NewData

FROM temp

ORDER BY id

Simple enough, You can use this output any where in further logic breakdowns as well as store output in any temp table or permanent table to use this data further.

Keywords:

1. Convert semicolon separated string in rows
2. Convert comma separated string in rows (just replace semicolumn with comma)
3. Demoralize data
4. Column to rows