separate values by new line and split to new row

Question

How to separate values by new line and split to new row in SQL Server. I have the following data

Ticket	Agent
98765	Agent1 Agent2 Agent3
12345	Agent4 Agent5 Agent6

Or as below

Now I have to convert it into the following -

Have you tried anything? Splitting values has been asked many, many, many times before — HoneyBadger, Mar 23 '21 at 11:17

score 1 · Accepted Answer · answered Mar 23 '21 at 11:17

1

select t.ticket, s.value as agent
from t cross apply
     string_split(t.agent, '
') s
order by s.value;

Here is a db<>fiddle.

answered Mar 23 '21 at 11:17

Gordon Linoff

1 Answers1