SQL – Splitting string in multiple columns

I’d like to split the result from a query and display the values in separate columns. As an example I get the following result

|Name          |
|ABC_DEFG_HIJKL|
|A_B_C         |
|A_B_C_D       |

I want to split the values by ‘_’ and add them to separate columns. The query result should look something like this

|Name          |first   |second   |third   |fourth|
|ABC_DEFG_HIJKL|ABC     |DEFG     |HIJKL   |null  |
|A_B_C         |A       |B        |C       |null  |
|A_B_C_D       |A       |B        |C       |D     |

So far I can split the result. But for each value, I have a new row. So I just need to merge the results into one row and make a column for each of those rows.

SELECT DP.Name, value  
FROM RitopDatenpunkt DP  
    CROSS APPLY STRING_SPLIT(DP.Name, '_'); 


|Name          |value   |
|ABC_DEFG_HIJKL|ABC     |
|ABC_DEFG_HIJKL|DEFG    |
|ABC_DEFG_HIJKL|HIJKL   |
|A_B_C         |A       |
|A_B_C         |B       |
|A_B_C         |C       |
|A_B_C_D       |A       |
|A_B_C_D       |B       |
|A_B_C_D       |C       |
|A_B_C_D       |D       |

I know that I should use PIVOT. But what aggragate function do I use and are the arguments for the FOR statement right

SELECT DP.Name, value  
FROM RitopDatenpunkt DP  
    CROSS APPLY STRING_SPLIT(DP.Name, '_')
PIVOT
(
        GROUPING(Name) as Name
        FOR value in ([first],[second],[third],[fourth])
)piv;

Answer

Here is one way to do it using JSON functions:

select t.name,
    json_value(x.obj, '$[0]') name1,
    json_value(x.obj, '$[1]') name2,
    json_value(x.obj, '$[2]') name2,
    json_value(x.obj, '$[3]') name4
from mytable t
cross apply (values('["' + replace(t.name, '_', '", "') + '"]')) x(obj)

The trick is to manipulate the string to make it look like a JSON array (that’ what the cross apply subquery does). Basically this turns a string like 'A_B_C' to '["A", "B", "C"]'. We can then use json_value() to easily access each individual element.

This does not assume anything about the elements being unique. Actually the only requirement is that the string should not contain embedded double quotes.

Demo on DB Fiddle:

name           | name1 | name2 | name2 | name4
:------------- | :---- | :---- | :---- | :----
ABC_DEFG_HIJKL | ABC   | DEFG  | HIJKL | null 
A_B_C          | A     | B     | C     | null 
A_B_C_D        | A     | B     | C     | D