问如何将带有逗号分隔符的行中的多个值转换为多列？

EN

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

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

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)

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