Update upper row data with next row data that was detached partially from import.

[fredong]

On the same table I have table rows that are broken from the import and went to the next row. I need to bring it back to the parent row. For example ID 1 Col3 supposed to have the value 'GHI' but the 'I' went to the 2nd row or ID 2 Col1 cell. Same applies to ID3 supposed to have 'GGG' but it went to the next row 4 or ID 4. Also, note that not every same filename has 2 rows some files have 1 row only but did not pose a problem. Any SQL statements of advise? This is MSSQL 2017. See attachment for Data table.

Thanks

 

[mikrom]

Hi fredong,

Suppose I would have a table called Fredong and this select

select * from fredong

delivers this result

ID   DOCNAME  COL1  COL2  COL3
 1    File1   ABC   DEF   GH  
 2    File1   I     JKH   LMN 
 3    File2   DDD   RRR   GG  
 4    File2   G     YYY   PPP 
 5    File3   TTT   CCC   MMM 
 6    File4   WWW   QQQ   ZZZ

To correct the data, I would try something like this:

select                                     
  t1.id as id,
  t1.DocName as DocName,
  case
    when length(strip(t1.col1)) = 1 then ''
    else t1.col1
  end as col1,
  t1.col2 as col2,
  case
    when length(strip(t2.col1)) = 1 then
      concat(strip(t1.col3), t2.col1)
    else t1.col3
  end as col3
from
  fredong t1 left join
  fredong t2 on t1.DocName = t2.DocName and
                t1.id != t2.id

which delivers this result

ID   DOCNAME  COL1  COL2  COL3
 1    File1   ABC   DEF   GHI 
 2    File1         JKH   LMN 
 3    File2   DDD   RRR   GGG 
 4    File2         YYY   PPP 
 5    File3   TTT   CCC   MMM 
 6    File4   WWW   QQQ   ZZZ

 

[fredong]

Mikron,
Thanks for your response. The strip and length functions are from python and I am using TSQL. I replaced it with LTRIM & TRIM and LEN. It did not resolved my problem but getting the same result. Any ideas?

select
t1.id as id,
t1.DocName as DocName,
case
when len(LTRIM(RTRIM(t1.col1))) = 1 then ''
else t1.col1
end as col1,
t1.col2 as col2,
case
when len(LTRIM(RTRIM(t2.col1))) = 1 then
concat(LTRIM(RTRIM(t1.col3)), t2.col1)
else t1.col3
end as col3
from
fredong t1 left join
fredong t2 on t1.DocName = t2.DocName and
t1.id != t2.id

 

[mikrom]

Hi
I tried the select I posted on DB2, the functions STRIP and LENGTH are available - see here:

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_bif_strip.html

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_bif_length.html

Unfortunately I don't have MS SQL.
Does the SELECT not work for you, or does it give the wrong result ?

 

[fredong]

Hi Mikrom,
The code did not return any error but the result is still the same where I am before. See my comments below --

select
t1.id as id,
t1.DocName as DocName,
case
when len(LTRIM(RTRIM(t1.col1))) = 1 then '' -- Length varies so it can be more than 1. I did > 1 but it empty my col1
else t1.col1
end as col1,
t1.col2 as col2,
case
when len(LTRIM(RTRIM(t2.col1))) = 1 then -- Length varies so it can be more than 1. I did > 1 but it empty my col1
concat(LTRIM(RTRIM(t1.col3)), t2.col1)
else t1.col3
end as col3
from
fredong t1 left join
fredong t2 on t1.DocName = t2.DocName and
t1.id != t2.id

 

[mikrom]

And works it with the example table you showed as an example on the picture ?

' -- Length varies so it can be more than 1. I did > 1 but it empty my col1

You gave only an example where only the last characters are separated into the next line, so I thought only about length of 1.

If all your string data in col3 should have have the some length N, then try to modify the conditions to

...
when length(trim(t1.col1)) < N then ...
...
when length(trim(t2.col1)) < N then ...
...

 

[carp]

This seems to work, but involves a number of queries from the database:

SELECT t1.id, t1.docname,t1.col1,t1.col2,t1.col3||t2.col1 AS col3, t2.col2 as col4, t2.col3 as col5
FROM (select * from fredong  WHERE docname IN (SELECT docname FROM fredong GROUP BY docname HAVING count(*) > 1)) t1
     INNER JOIN (SELECT * FROM fredong ) t2
       ON t1.docname = t2.docname
          AND t1.id < t2.id
UNION ALL
SELECT id, docname,col1,col2,col3,null,null FROM fredong WHERE docname IN (SELECT docname FROM fredong GROUP BY docname HAVING count(*) = 1)
 order by docname;