Split Comma Separated values into multiple column

Question

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

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

Something seems a bit off here. Why or how are you deciding which column the data ends up in? for this simplified example it is easy but I doubt your real data is so simple. — Sean Lange, Feb 17 '20 at 15:28
And to Sean's point, please [edit](https://stackoverflow.com/posts/60265620/edit) your question and add some more representative data as text, not images, to help us help you. — Eric Brandt, Feb 17 '20 at 15:32
This doesn't look like want want to split anything, and looks more like you want to have a column with a value *if* the other value has the value in it. If you *split* the data, then your final row would have a value `4` in `col1` and `NULL` in the others (not `4` in `col4` and `NULL` in the others). — Thom A, Feb 17 '20 at 15:36
Does this answer your question? [How to split a comma-separated value to columns](https://stackoverflow.com/q/10581772/2029983) — Thom A, Feb 17 '20 at 15:38
in reality A single cell can have number ranging from 1 to 8 separated with comma (no repetition), and as we can have maximum 8 different values we'll add 8 columns to accommodate the splitted data. — abhi8569, Feb 17 '20 at 15:38
I do hope this effort is to fix the design. Storing delimited data like this violates 1NF and is nothing short of horrific to work with. — Sean Lange, Feb 17 '20 at 15:55
I would like to fix the structure but unfortunately it's too late to do that. BTW I am creating a kind of fact table where I need to have separate column for reporting purpose. — abhi8569, Feb 18 '20 at 07:36

score 3 · Accepted Answer · answered Feb 17 '20 at 15:40

3

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

answered Feb 17 '20 at 15:40

Sean Lange

33,028
3
25
40

1

Beat me to it. :) – Thom A Feb 17 '20 at 15:41
1

@Larnu that's a first. :D – Sean Lange Feb 17 '20 at 15:41
@Sean Lange How about `('11,2,3')` ? https://i.stack.imgur.com/4bt73.png – Nguyễn Văn Phong Feb 17 '20 at 15:50
That isn't in the sample data. Only had values 1-4. And then in the comments they said they have 1-8. So how does 11 come into play? To be honest the question is so over simplified it isn't even funny. I provided a solution to the example provided. – Sean Lange Feb 17 '20 at 15:53
Yes, I mean we can provide the OP more option with the best solution as well as possible. Check [my answer](https://stackoverflow.com/a/60265888/9071943) to have more options. Even if, I'm considering the dynamic query instead of **hard code** like that. Do you have any idea? – Nguyễn Văn Phong Feb 17 '20 at 15:55
@Phong sure we can use STRING_SPLIT as your example. But with the crazy simple data it is a bit overkill. Not saying your's is not a good solution though. – Sean Lange Feb 17 '20 at 15:57
1

Was going to suggest exactly this. – KeithL Feb 17 '20 at 18:31

Nguyễn Văn Phong · Answer 2 · 2020-02-17T15:52:08.850

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

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

Does this answer your question? Let me know if you need any help @abhi8569 — Nguyễn Văn Phong, Feb 18 '20 at 07:38

Split Comma Separated values into multiple column

2 Answers2