Comma Separated Value to Multiple BIT Columns

Question

Hy , I have multiple values and i need a list of checks
ex:
1,2,4,
3,4, should be :

day1 day2 day3 day4  
_1____1____0____1
_0____0____1____1

one method is

CAST(CASE WHEN PATINDEX('1,', [day]) > 0 THEN 1 ELSE 0 END AS BIT) as [day1],  
CAST(CASE WHEN PATINDEX('2,', [day]) > 0 THEN 1 ELSE 0 END AS BIT) as [day2],  
CAST(CASE WHEN PATINDEX('3,', [day]) > 0 THEN 1 ELSE 0 END AS BIT) as [day3],  
CAST(CASE WHEN PATINDEX('4,', [day]) > 0 THEN 1 ELSE 0 END AS BIT) as [day4]

please help me with a better method because i have multiple columns
thanks

Here is an article about it: http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows — Chris, Nov 30 '12 at 14:14
@Chris I know I can return a table with multiple rows but I need multiple `Columns` — Zyku, Nov 30 '12 at 14:15

score 1 · Accepted Answer · answered Nov 30 '12 at 15:06

You can use a string split function of your choice with pivot.

declare @T table
(
  ID int identity,
  day varchar(20)
)

insert into @T values
('1,2,4,'),
('3,4,')

select isnull(P.[1], 0) as day1,
       isnull(P.[2], 0) as day2,
       isnull(P.[3], 0) as day3,
       isnull(P.[4], 0) as day4
from 
  (
  select T.ID, S.s, 1 as x
  from @T as T
    cross apply dbo.Split(',', T.day) as S
  ) as T
pivot (min(T.x) for T.s in ([1],[2],[3],[4])) as p

Result:

day1  day2  day3  day4
1     1     0     1
0     0     1     1

score 0 · Answer 2 · edited May 23 '17 at 10:24

0

You could use the split function outlined here and then pivot the values into columns.

OR!

This answer looks like the same thing you're trying to do.

edited May 23 '17 at 10:24

Community

1
1

answered Nov 30 '12 at 14:17

Nick Vaccaro

5,428
6
38
60

Isaac · Answer 3 · 2012-11-30T15:16:47.203

Use a second table to store valid days and then query on it. See this fiddle.

EDIT See updated fiddle, or code below:

create table test (day1 varchar(8), day2 varchar(8), day3 varchar(8), day4 varchar(8));
insert into test (day1, day2, day3, day4) values ('day1', 'day2', 'day3', 'day4');

create table valid_days (valid_day_no int);
insert into valid_days values (1), (2), (4);

select cast (case when exists(select 1 from valid_days where valid_day_no = substring(day1, 4, len(day1))) then 1 else 0 end as bit) day1,
       cast (case when exists(select 1 from valid_days where valid_day_no = substring(day2, 4, len(day2))) then 1 else 0 end as bit) day2,
       cast (case when exists(select 1 from valid_days where valid_day_no = substring(day3, 4, len(day3))) then 1 else 0 end as bit) day3,
       cast (case when exists(select 1 from valid_days where valid_day_no = substring(day1, 4, len(day4))) then 1 else 0 end as bit) day4
from test;

Note that SQL is a fixed column language, you need to use a programming language to dynamically build variable length column queries.

('day1'), ('day2'), ('day3'), ('day4'); these should be `COLUMNS` — Zyku, Nov 30 '12 at 14:46

Comma Separated Value to Multiple BIT Columns

3 Answers3