SQL Server How can I parse a data from one table to another table based on conditions

xxe27gdn 于 2023-03-11 发布在其他

关注(0)|答案(1)|浏览(184)

Given two tables A and B, where table A contains data about segments in a document and table B contains document names and their corresponding beginning segment values.((table a and table b are temp tables, table a will have values fetch or parse from input document))

Table A
(line_no (INT), segment(VARCHAR(10)
1   ISA 
2   GS  
3   ST  
4   BCH --//this is beginning segment of doc
9   N1      --this is a document1
10  N3  
11  N4  
12  N1  
13  SE  

14  ST  
15  BEG --//this is beginning segment of doc
16  N1  ---this is document2
17  N3  
18  N4  
19  SE  

20  ST  
21 BRA--//this is beginning segment of doc
22  N1  
23  N3  --this is document3
24  N4  
25  SE  

26  ST  
27  BCX--//this is beginning segment of doc
28  N1  
29  N3  --this is a document4
30  N4  
31  SE      
32  GE  
33 IEA

Table A has columns line_no and segment. Each row in this table represents a segment in a document. The ST segment marks the beginning of a new document.

Table B has columns Document_name and segment. Each row in this table represents a document name and its corresponding beginning segment value.

Table B
Document_name(VARCHAR(10)), segment(VARCHAR(10))
('810','BIG'),
('824','BGN'),
('861','BRA'),
('862','BSS'),
('850','BEG'),
('820','BPR'),
('830','BFR'),
('860','BCH')

I am trying to write a query, if document in table A has a valid beginning segment value immediately following the ST segment. If the beginning segment value from table a is not present in table B, the document is considered invalid. If the beginning segment value is present in table B, the document is considered valid. (Note : beginning segment values will be always follow by "ST" Segment)

I have written like this but its not near to what I am expecting

SELECT 
  a.Segment_Identifier, 
  CASE WHEN EXISTS (SELECT * FROM @BCH_Segment_Table AS b WHERE b.beg_segment = a.Segment_Identifier)
       THEN 'Valid' 
       ELSE 'Invalid'
  END AS NewFiled  
FROM @Segment_Table as a

Output should be be show for four document(based on table a (each ST will represent one document)) not for individual segment is :

document 1 :valid
document 2 :valid
document 3 :valid
document 4 :invalid

sql-server

来源：https://stackoverflow.com/questions/75594056/how-can-i-parse-a-data-from-one-table-to-another-table-based-on-conditions

1条答案

按热度按时间

00jrzges1#

Something like this very rough script perhaps:

select *
, (select top 1 1 as exists from tableB b WHERE b.segment = s.segment) AS segmentExistsInB
from (
  select *
  , lag(segment) over(order by lineno) as prevSegment
  from tableA a
) s
where s.prevSegment = 'ST'

I identify rows where previous segment name is ST and then check if the value exists in tableB.

赞(0）回复(0）举报 2023-03-11

我来回答

SQL Server How can I parse a data from one table to another table based on conditions

1条答案

相关问题

热门标签

最新问答