×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

thread220-1800625

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

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

(OP)
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

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

Hi fredong,

Suppose I would have a table called Fredong and this select

CODE

select * from fredong

delivers this result

CODE

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:

CODE

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

CODE

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

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

(OP)
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

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

(OP)
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

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

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

Quote:


' -- 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

CODE

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

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

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

CODE

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;

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close