Split Characters in a cell to individual columns [closed]-CodePudding

I have a dataset where a column contains 1 and 0 in a single cell which reference security authorities in a software program. I need to split these out into individual columns as these denote (0) No Access and (1) Access

Example:

Column1
01001

Expected Outcome

Column1 Column2 Column3 Column4 Column5
0       1       0       0       1

Appreciate any help on this sql statement to make this happen

Thanks Paul

CodePudding user response：

Making an assumption that you would have clarified if the length were variable (which would be cruicial information to include) and that the value is always 5 characters as indicated in the question title.

You can use a set of conditional case expressions to pivot the data after it's split into rows using substring

with n as (select n from (values (1),(2),(3),(4),(5))v(n))
select 
    max(case when n=1 then c end) as column1,
    max(case when n=2 then c end) as column2,
    max(case when n=3 then c end) as column3,
    max(case when n=4 then c end) as column4,
    max(case when n=5 then c end) as column5
from t
cross join n
cross apply (values (Substring(col1,n,1)))v(c)

Demo DB<>Fiddle

CodePudding user response：

With the assumptions proposed by @Stu, a simplified version would be

declare @v varchar(5) = '01001'

select 
  substring(@v,1,1) as column1,
  substring(@v,2,1)  as column2,
  substring(@v,3,1)  as column3,
  substring(@v,4,1)  as column4,
  substring(@v,5,1) as column5