Converting string to Columns with if condition-CodePudding

Trying to create columns based on the integer value. There are two scenarios. Weekdays are starting from either 0 or 1. So I am trying to figure it out how to apply logic to create columns out of the data provided.

Expected output is

Mon Tue Wed Thu Fri Sat Sun
1    0   0   0   0   0   0
0    0   0   0   0   1   0
0    1   1   1   0   0   1

Tried

SELECT top 1000
(select Monday = If (LEFT(CAST(#ABC.Working as varchar(1)),1)) as Mon,
(select Monday = LEFT(CAST(#ABC.Working as varchar(1)),2)) as Tue,
(select Monday = LEFT(CAST(#ABC.Working as varchar(1)),3)) as Wed,
(select Monday = LEFT(CAST(#ABC.Working as varchar(1)),4)) as Thu,
(select Monday = LEFT(CAST(#ABC.Working as varchar(1)),5)) as Fri,
(select Monday = LEFT(CAST(#ABC.Working as varchar(1)),6)) as Sat,
(select Monday = LEFT(CAST(#ABC.Working as varchar(1)),7)) as Sun
from WorkingTable #ABC

Expecting the following

New Question. If the WorkingTable has the following data, where we see 0, then in this case, consider 0 as Monday and 6 as Sunday. . Otherwise 1 as Monday and 7 as Sunday.

Tried


SELECT top 1000
    Working,
    CASE WHEN Working LIKE '%0%'
    THEN
    ( 
       CASE WHEN Working LIKE '%0%' THEN 1 ELSE 0 END as Mon,
       CASE WHEN Working LIKE '%1%' THEN 1 ELSE 0 END as Tue,
       CASE WHEN Working LIKE '%2%' THEN 1 ELSE 0 END as Wed,
       CASE WHEN Working LIKE '%3%' THEN 1 ELSE 0 END as Thu,
       CASE WHEN Working LIKE '%4%' THEN 1 ELSE 0 END as Fri,
       CASE WHEN Working LIKE '%5%' THEN 1 ELSE 0 END as Sat,
       CASE WHEN Working LIKE '%6%' THEN 1 ELSE 0 END as Sun
     )
     ELSE
     (
       CASE WHEN Working LIKE '%1%' THEN 1 ELSE 0 END as Mon,
       CASE WHEN Working LIKE '%2%' THEN 1 ELSE 0 END as Tue,
       CASE WHEN Working LIKE '%3%' THEN 1 ELSE 0 END as Wed,
       CASE WHEN Working LIKE '%4%' THEN 1 ELSE 0 END as Thu,
       CASE WHEN Working LIKE '%5%' THEN 1 ELSE 0 END as Fri,
       CASE WHEN Working LIKE '%6%' THEN 1 ELSE 0 END as Sat,
       CASE WHEN Working LIKE '%7%' THEN 1 ELSE 0 END as Sun
     )
     END
from WorkingTable;

CodePudding user response：

I think you can use LIKE condition for this problem:

SELECT top 1000
    Working,
    CASE WHEN Working LIKE '%1%' THEN 1 ELSE 0 END as Mon,
    CASE WHEN Working LIKE '%2%' THEN 1 ELSE 0 END as Tue,
    CASE WHEN Working LIKE '%3%' THEN 1 ELSE 0 END as Wed,
    CASE WHEN Working LIKE '%4%' THEN 1 ELSE 0 END as Thu,
    CASE WHEN Working LIKE '%5%' THEN 1 ELSE 0 END as Fri,
    CASE WHEN Working LIKE '%6%' THEN 1 ELSE 0 END as Sat,
    CASE WHEN Working LIKE '%7%' THEN 1 ELSE 0 END as Sun
from WorkingTable;

SQLize - sql code online