CODEWITHSUNDEEP
sql-server-2008t-sql
Mursa Catalin
Mursa Catalin

Reputation: 1449

Comma Separated Value to Multiple BIT Columns

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

Upvotes: 0

Views: 345

Answers (3)

Isaac
Isaac

Reputation: 2721

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.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 1

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

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.

Upvotes: 0

Related Questions