--EDIT: original table sample, requested in comments

This is a C# reflection of an object to serialise the data in the Change field.

The desired result is the following:

I managed to identify the character as CHAR(1), in order to be able to split it using the following query (which lead to the unpivoted table, which might or might not be useful- apparently not as per comments below, since the order is uncertain):

SELECT job_id, change_id, VALUE change 
FROM change_table
CROSS APPLY STRING_SPLIT(change,CHAR(1))

CodePudding user response：

It's kind of painful when delimited data has a trailing delimiter. Here is a simple solution to this using PARSENAME. I had to add and extra space back on the end here because the PARSENAME function gets confused when the last character is a period.

declare @Changes table
(
    job_id int
    , change_id int
    , change varchar(20)
)

insert @Changes values
(1, 1, '5 6 ')
, (1, 2, '7 8 ')
, (1, 3, '9 10 ')
, (2, 4, '1 3 ')

select c.job_id
    , c.change_id
    , ChangeFrom = parsename(replace(c.change, ' ', '.')   ' ', 3)
    , ChangeTo = parsename(replace(c.change, ' ', '.')   ' ', 2)
from @Changes c

CodePudding user response：

Assuming, the Changes value of the last of three rows is ''.

Does this work for you?

SELECT 
    *,
    '' blank
FROM (    
    SELECT 
        job_id,
        change_id,
        changes AS changes_from,
        LEAD(changes) OVER (PARTITION BY job_id, change_id ORDER BY job_id) AS changes_to
   FROM jobs
) j
WHERE changes_from != '' AND changes_to != ''

Output

db<>fiddle here