1

--EDIT: original table sample, requested in comments

job_id change_id change
1 1 5□6□
1 2 7□8□
1 3 9□10□
2 4 1□3□

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

The desired result is the following:

Job ID Change ID Change from Change to
1 1 5 6
1 2 7 8
1 3 9 10
2 4 1 3

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))
Job ID Change ID Changes
1 1 5
1 1 6
1 1
1 2 7
1 2 8
1 2
1 3 9
1 3 10
1 3
2 4 1
2 4 3
2 4
kai chapter
  • 27
  • 5
  • 2
    Do you have a primary key or any other column that can be used to order table rows? – Alexey Aug 30 '22 at 15:00
  • How about you show us the base table with sample data, and the query you used to generate this. – Charlieface Aug 30 '22 at 15:23
  • The Change ID column is a primary key in the change table, but in that table I have the changes logged using a C# reflection of an object to serialise data. It is something like "1[]2[]" where those characters are not pasted well, but I managed to split by that unrecognised character, ending up with 1,2 and blank as the values, which I now want to put back in one row per change. – kai chapter Aug 30 '22 at 15:25
  • 1
    You need something that indicates the order of the rows because going from and to indicates the order is important. Without something in the data to order your rows are going to be different orders sometimes when you run your query. – Sean Lange Aug 30 '22 at 15:32
  • I have updated the question to include a data sample of the original table – kai chapter Aug 30 '22 at 15:38
  • The original table is the change table and the primary key is change_id. The job_id is a foreign key to a job table. I am hoping that once I manage to "re-pivot" this table I can join on anything else I want to add data where needed, using these two columns. – kai chapter Aug 30 '22 at 15:50
  • What would really help here is table structures (create table statements), and consumable data (insert statements), followed by desired output based on the sample data. One easy way to provide the table and sample data is with a sql fiddle. You can do that here. https://dbfiddle.uk/ – Sean Lange Aug 30 '22 at 15:57
  • 1
    you are right, I am sorry, I made a mistake in making up this dummy data. I have updated the data. – kai chapter Aug 30 '22 at 15:57
  • You can try something like this - https://stackoverflow.com/a/58836025/125551 on your original table instead of trying it to split and then convert them back to columns, the example uses xml – rs. Aug 30 '22 at 16:02
  • I don't know how to make up this data through table statements unfortunately, it's taken from C# in ways that I don't really understand. That sample is pretty much all I have across millions of rows, all following this same strange pattern of number char(1) number char(1), e.g. 2□3□ – kai chapter Aug 30 '22 at 16:05

2 Answers2

1

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

job_id change_id changes_from changes_to blank
1 1 5 6
1 1 7 8
1 2 9 10
2 3 1 3

db<>fiddle here

Alexey
  • 2,439
  • 1
  • 11
  • 15
  • You need something better than job_id to order by. This will work on small table where the order is accidentally the same. But given the data from the OP this is about as good as it could be. – Sean Lange Aug 30 '22 at 15:30
  • @SeanLange, Absolutely agree with you. But it looks like the OP doesn't have such a column to order the rows in his table. – Alexey Aug 30 '22 at 15:34
  • exactly why I said it was as good as it could be. My comment was really more pointed to the OP than you. And also why I gave you a +1. ;) – Sean Lange Aug 30 '22 at 15:37
  • It is not working for me unfortunately. It did pivot but it also duplicated the row, so that I ended up with the first line as correct (having changes_from and changes_to) and the second line still exists unmodified, it was not removed, but it has a blank in the changes_to column. – kai chapter Aug 30 '22 at 15:53
  • By the way, the 3rd value is not actually NULL, it is blank I believe, it's the result of splitting that initial serialised data which doesn't translate very well in SQL – kai chapter Aug 30 '22 at 15:55
  • Would you be able to update this answer to account for that? So that the NULL is in fact blank, I believe that's the problem. – kai chapter Aug 30 '22 at 16:08
  • Thank you, this also seem to work. But, as it has been said, I am not sure if the order is guaranteed. – kai chapter Aug 30 '22 at 16:44
1

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
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • Thank you, that is wonderful. I just replaced the blanks with CHAR(1) and it works flawlessly. – kai chapter Aug 30 '22 at 16:21
  • I am trying to read in the documentation about this function, but I am really confused on why ChangeFrom is 3 and ChangeTo is 2. It works well, but can you update me on why it is not 1 and 2? Which indeed doesn't work. – kai chapter Aug 30 '22 at 16:55
  • Take a closer look at the documentation. https://learn.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql?view=sql-server-ver16 It parses backwards of what you would think. 4 part naming goes like this. Server.Database.Schema.Name. So in your case you want the right most value of ParseName as the "From". But since there is only 3 parts in our built up string that would be 3 instead of the usual 4. You could force it have 4 parts by adding ".x" at the end instead of the space so it would have all parts. Then you would use 4 for From and 3 for To. Does that help? – Sean Lange Aug 30 '22 at 18:04
  • It does help, yes. Does this work with more than 4 arguments and for any string? Or is it a forced association with Server.Database.Schema.Name, and it is designed for that only? – kai chapter Aug 30 '22 at 21:01
  • Did you read the documentation in the link I posted? It is designed for parsing object names, as such they can only have up to four parts. – Sean Lange Aug 31 '22 at 13:12