-1

I am trying to take one column in my table that has multiple values (up to 10) delimited by a "pipe" "|" in it and add the delimited values into additional columns in the table. Note (running on SQL SVR 2014).

Table ...

Col1   Col2   Col3
1      Tom    12345678|87654321|11111111|22222222|..... up to 10
2      Joe    14563467
3      Zac    12345678|87654321

I need the results of SQL to produce

Col1   Col2   Col3      Col4      Col5      Col6      Col7   Col8   Col9 ....
1      Tom    12345678  87654321  11111111  22222222
2      Joe    14563467
3      Zac    12345678  87654321

Any help is appreciated!

Ilyes
  • 14,640
  • 4
  • 29
  • 55
BradM
  • 21
  • 3
  • I would suggest that this has already been answered in https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns – Sean Apr 08 '20 at 15:24

2 Answers2

0

You can first normalize your data using the new STRING_SPLIT function into a derived table with the split columns stretched downward. Using that table you can PIVOT out based on Col1 to create the 5 columns basically pulling the data back up that was previously split down. Next, use that data as the source for you update back to the source table.

If you are not on SQL Server 2016 then you will need to replace STRING_SPLIT with an delimited string parser Table Value Function . 

DECLARE @T TABLE(Col1 INT, Col2 NVARCHAR(50), Col3 NVARCHAR(50), Col4 NVARCHAR(50), Col5 NVARCHAR(50))

INSERT @T (COl1,Col2) VALUES (1,'12345678|87654321|11111111|22222222|')
INSERT @T (COl1,Col2) VALUES (2,'12345678')
INSERT @T (COl1,Col2) VALUES (3,'12345678|87654321|')

SELECT * FROM @T
;
WITH SplitData AS
(
    SELECT 
        Col1,Col2 = S.Value,
        RN = ROW_NUMBER()OVER( PARTITION BY Col1 ORDER BY (SELECT 1 AS X))
    FROM 
        @T
        CROSS APPLY STRING_SPLIT(Col2,'|') S
)
,UpdateData AS
(
    SELECT 
        Col1, Col2=[1], Col3=[2], Col4=[3], Col5=[4] 
    FROM  
    (
        SELECT Col1, Col2, RN FROM SplitData
    ) AS S  
    PIVOT(
        MAX(Col2) FOR RN IN ([1], [2], [3], [4], [5])
    ) AS P
)
UPDATE L 
SET L.Col1 = R.Col1, L.Col2=R.Col2, L.Col3=R.Col3, L.Col4=R.Col4, L.Col5 = R.Col5
FROM 
    @T L
    INNER JOIN UpdateData R ON L.Col1 = R.Col1

SELECT * FROM @T
Ross Bush
  • 14,648
  • 2
  • 32
  • 55
  • Ross , my team is on SQL server 2014, what is a good delimited string parser Table Value Function for this case? – BradM Apr 13 '20 at 15:16
  • You will get many examples, however, I would search for most performant string split routines for sql server. There are some pretty good articles in which some people spent a lot of time comparing algorithms. – Ross Bush Apr 13 '20 at 15:35
0

If u are sure max values count in Col3 is 10 u can use cross apply with split string on Col3 and select columns with splited value and row number partitioned by col1 and col2. Secound step is pivot this table by row

        WITH SplitTable AS(
        SELECT Col1
              ,Col2
              ,value 
              ,ROW_NUMBER() OVER(PARTITION BY Col1, Col2 ORDER BY (SELECT NULL)) as rn
        FROM YOUR_TABLE 
            CROSS APPLY STRING_SPLIT(Col3, '|') AS YT
        )
        SELECT Col1
              ,Col2
              ,[1]
              ,[2]
              ,[3]
              ,[4]
              ,[5]
              ,[6]
              ,[7]
              ,[8]
              ,[9]
              ,[10]
        FROM SplitTable
        PIVOT(
            MAX(VALUE)
            FOR RN IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])  
        ) as PVT
Pyra
  • 31
  • 4