Thursday, August 9, 2018

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



web stats