CODEWITHSUNDEEP
sqlsql-serverssis
abhi8569
abhi8569

Reputation: 131

Split Comma Separated values into multiple column

I have a requirement where I have a column in my SQL table in this format:

SQL Column

How to split the data using comma delimiter and insert it into newly added columns in the same table?

enter image description here

Upvotes: 0

Views: 1973

Answers (2)

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14228

Demo on db<>fiddle

It seems to me you need to use CASE WHEN END to achieve it.

select value, case when CHARINDEX('1', value) > 0 then '1' else '' end col1,
       case when CHARINDEX('2', value) > 0 then '2' else '' end col2,
       case when CHARINDEX('3', value) > 0 then '3' else '' end col3,
       case when CHARINDEX('4', value) > 0 then '4' else '' end col4
from #a

Output

enter image description here

Updated Demo

In case of the value may be ('11,2,3'), You should use STRING_SPLIT like below to get the exact result.

select value, 
case when EXISTS(SELECT TOP 1 1 FROM STRING_SPLIT(value, ',') s where s.value = '1') then '1' else '' end col1,
case when EXISTS(SELECT TOP 1 1 FROM STRING_SPLIT(value, ',') s where s.value = '2') then '2' else '' end col2,
case when EXISTS(SELECT TOP 1 1 FROM STRING_SPLIT(value, ',') s where s.value = '3') then '3' else '' end col3,
case when EXISTS(SELECT TOP 1 1 FROM STRING_SPLIT(value, ',') s where s.value = '4') then '4' else '' end col4
from #a

enter image description here

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33581

Your sample data may not need any splitting. You want to move the data to a column based on the value it finds. You can do this a bit simpler than splitting the data. This works just fine for your sample data.

declare @Something table
(
    Combined_Column varchar(10)
)

insert @Something values
('1,2,3')
, ('2')
, ('1,3')
, ('1,2,3,4')
, ('1,3,4')
, ('1')
, ('4')

select *
    , col1 = case when charindex('1', s.Combined_Column) > 0 then 1 end
    , col2 = case when charindex('2', s.Combined_Column) > 0 then 2 end
    , col3 = case when charindex('3', s.Combined_Column) > 0 then 3 end
    , col4 = case when charindex('4', s.Combined_Column) > 0 then 4 end
from @Something s

Upvotes: 3

Related Questions