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

如果您有有限或最大列数，另一种选择

示例

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

编辑-只是为了好玩，这里是上述 的动态版本

只需将 YourTable 替换为实际的表名称，将 YourCol 替换为所需的要拆分的列。

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
                    For XML Path ('')),1,1,'')
Set @SQL = '
Select A.*,B.*
 From  YourTable A