How can I convert comma-separated values stored in SQL Server to actual individual columns

Question

What type of TSQL statement can be used to read from data in Table1 (below) and produce the Expected Results (below):

With the following data in Table1:

ID      Field1      Field2      Field3
----------------------------------------------------------------
1       R1F1        R1F2        R1F3, R1F4, R1F5, R1F6
2       R2F1        R2F2        R2F3, R2F4, R2F5, R2F6

Expected Results A SQL Statement that would produce the following results:

ID      Field1      Field2      Col1    Col2    Col3    Col4
----------------------------------------------------------------
1       R1F1        R1F2        R1F3    R1F4    R1F5    R1F6
2       R2F1        R2F2        R2F3    R2F4    R2F5    R2F6

Does this answer your question? [How to split a comma-separated value to columns](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — sticky bit, Aug 08 '20 at 01:07
I’m voting to close this question because it lacks both a question and any indication of effort. The topic is readily amenable to searching for a suitable approach. — HABO, Aug 08 '20 at 03:58

score 2 · Accepted Answer · answered Aug 08 '20 at 05:01

You can also use parsename (limited to 4 parts only) or string_Split like below:

Select t.Field1, t.Field2
      ,Col1  = parsename(B.NewStr,4)
      ,Col2  = parsename(B.NewStr,3)
      ,Col3  = parsename(B.NewStr,2)
      ,Col4 = parsename(B.NewStr,1)
 From  @t t
 Cross Apply (values (replace(Field3,',','.') ) ) B(NewStr)

Metohd2:

;WITH C AS(
SELECT t.Field1, t.Field2
      ,value 
      ,ROW_NUMBER() OVER(PARTITION BY t.Field3 ORDER BY (SELECT NULL)) as rn
FROM @t t
    CROSS APPLY STRING_SPLIT(Field3, ',') AS SS
)
SELECT Field1, Field2
      ,[1] AS Col1
      ,[2] AS Col2
      ,[3] AS Col3
      ,[4] AS Col4
FROM C
PIVOT(
    MAX(VALUE)
    FOR RN IN([1],[2],[3],[4])  
) as PVT

Please find the db<>fiddle here.

If no more than 4 columns, PARSENAME() is the way to go. +1 – John Cappelletti Aug 08 '20 at 12:13 — John Cappelletti, Aug 08 '20 at 12:13

How can I convert comma-separated values stored in SQL Server to actual individual columns

1 Answers1