如何分割逗号分隔值到各自的列在SQL Server?

Question

我有一个表，其中有逗号分隔的值。我需要将这些值拆分到它们各自的列中。

取值范围为1 ~ 10之间的任意数字。

例如，值'1,3,4'应该分割并将1放在第一列，3放在第三列，4放在第四列。

我试过下面的SQL查询。但是它没有放在正确的列中。

SELECT MemberTransId,
AccessZone,
ISNULL([1],'') AS [1],
ISNULL([2],'') AS [2],
ISNULL([3],'') AS [3],
ISNULL([4],'') AS [4],
ISNULL([5],'') AS [5],
ISNULL([6],'') AS [6],
ISNULL([7],'') AS [7],
ISNULL([8],'') AS [8],
ISNULL([9],'') AS [9],
ISNULL([10],'') AS [10],
ISNULL([11],'') AS [11]
FROM (
SELECT MemberTransId,
AccessZone,
CAST(ROW_NUMBER()OVER
(PARTITION BY MemberTransId ORDER BY MemberTransId) AS VARCHAR) AS Col,Split.value
FROM dbo.tbl_MemberTransaction AS Emp
CROSS APPLY String_split(AccessZone,',') AS Split
) AS tbl
Pivot (Max(Value) FOR Col IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11])
) AS Pvt

这是当前输出的截图

Answer 1

两个选项，一个为空，另一个不

Declare @YourTable table (MemberTransId int,AccessZone varchar(50))
Insert Into @YourTable values 
(2,',1,2,3,4')
,(3,',4,5')
-- With NULL values
Select *
From  (
Select A.MemberTransId
,Col = B.value
,Val = B.value
From  @YourTable A
Cross Apply string_split(AccessZone,',')B
) src
Pivot ( max(Val) for Col in ([1],[2],[3],[4],[5]) ) pvt

-- Without Null Values
Select MemberTransId
,[1] = Isnull([1],'')
,[2] = Isnull([2],'')
,[3] = Isnull([3],'')
,[4] = Isnull([4],'')
,[5] = Isnull([5],'')
From  (
Select A.MemberTransId
,Col = B.value
,Val = B.value
From  @YourTable A
Cross Apply string_split(AccessZone,',')B
) src
Pivot ( max(Val) for Col in ([1],[2],[3],[4],[5]) ) pvt