Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

fredong

IS-IT--Management
Sep 19, 2001
332
US
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

 
 https://files.engineering.com/getfile.aspx?folder=442c3229-1c8d-459e-9551-4255e2a24743&file=table_with_detached_data.png
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
 
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
 
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
 
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
Code:
...
when length(trim(t1.col1)) < N then ...
...
when length(trim(t2.col1)) < N then ...
...

 
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;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top