I have a table with comma-separated values in it. I need to split that values into their respective columns.

The values can be any number starting from 1 to 10.

For example, the value '1,3,4' should split and place 1 in the 1st column, 3 in the 3rd column, and 4 in the 4th column.

I have tried below SQL query. but it's not placed in the correct column.

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

Here is the current output screenshot

CodePudding user response：

Two options, one with nulls the other without

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