How to convert multiple values in the row with comma separator into multiple columns?

Question

How to convert multiple comma separated values in rows into multiple columns in SQL Server like I have a table with two rows

A1,1,B1,2 
C1,3,D4,4  

I want output like this

col1 col2 col3 col4
 A1   1     B1   2
 C1   3     D4   4

Answer 1

Another option if you have a finite or max number of columns

Example

Declare @YourTable Table ([YourCol] varchar(50))
Insert Into @YourTable Values 
 ('A1,1,B1,2')
,('C1,3,D4,4')

Select B.*
 From @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(A.YourCol,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as x
             ) B

Returns

Pos1    Pos2    Pos3    Pos4    Pos5    Pos6
A1      1       B1      2       NULL    NULL
C1      3       D4      4       NULL    NULL

EDIT - Just for Fun, Here is a Dynamic Version of the Above

Just replace YourTable with your actual table name, and YourCol with the desired column to split.

Declare @SQL nvarchar(max)
Set @SQL = Stuff((Select concat(',Col',N,' = ltrim(rtrim(xDim.value(''/x[',N,']'',''varchar(max)'')))') 
                   From  (
                           Select Top ((Select max(len(YourCol)-len(replace(YourCol,',','')))+1 From YourTable)) 
                                  N=Row_Number() Over (Order By (Select NULL)) 
                            From  master..spt_values
                          ) A 
                    For XML Path ('')),1,1,'')
Set @SQL = '
Select A.*,B.*
 From  YourTable A
 Cross Apply ( Select ' + @SQL +' From  (Select Cast(''<x>'' + replace((Select replace(A.YourCol,'','',''§§Split§§'') as [*] For XML Path('''')),''§§Split§§'',''</x><x>'')+''</x>'' as xml) as xDim ) x ) B
'
--Print @SQL
Exec(@SQL)

Returns

YourCol     Col1    Col2    Col3    Col4
A1,1,B1,2   A1      1       B1      2
C1,3,D4,4   C1      3       D4      4

Answer 2

Assuming your table name is t, you can follow the steps below

Step 1: Split the columns using the CSV tally type splitter

Step 2: PIVOT out the values

In a single query the solution will be

select * from 
(
select 
    t.col as col,
    row_number() over (partition by t.col order by t1.N asc) as row_num,
    SUBSTRING( t.col, t1.N, ISNULL(NULLIF(CHARINDEX(',',t.col,t1.N),0)-t1.N,4000)) as split_values
from t 
    join
    (
        select 
            t.col,
            1 as N 
        from t  
            UNION ALL
        select 
            t.col,
            t1.N + 1 as N
        from t 
            join
            (
             select 
                top 4000
                    row_number() over(order by (select NULL)) as N 
             from 
                sys.objects s1 
                    cross join 
               sys.objects s2 
            ) t1 
        on SUBSTRING(t.col,t1.N,1) = ','
     ) t1
     on t1.col=t.col
)src
PIVOT
( max(split_values) for row_num in ([1],[2],[3],[4],[5],[6],[7],[8]))p

working demo

PS: You can actually use a dynamic pivot if you do not know the maximum commas in the columns.