I need a few pointers on solving this
For example tblStudent
ID SNUM Name Grade
1 ST01
2 ST02
3 ST03
4 ST04
5 Simon
6 Kim
7 Jessica
8 Dale
9 12
10 11
11 11
12 10
And so on... The pattern is pretty obvious... I need either an update query of its own table or an insert query to a new table that look something like
ID SNUM Name Grade
1 ST01 Simon 12
2 ST02 Kim 11
3 ST03 Jessica 11
4 ST04 Dale 10
Can anyone point me a direction as to how to solve this problem?
In order to obtain the desired resultset, try
select t1.id
, t1.SNUM
, t2.name
, t3.grade
from (
select count(*) cnt
from tblStudent tagg
where tagg.SNUM is not null
) agg
cross join tblStudent t1
join tblStudent t2 on ( t2.id = t1.id + agg.cnt )
join tblStudent t3 on ( t3.id = t2.id + agg.cnt )
where t1.SNUM IS NOT NULL
and t2.name IS NOT NULL
and t3.grade IS NOT NULL
;
Explanation:
Basically, the SQL first counts the number of target records and splits the original table into the obvious 3 parts. these are joined and from each one of them, partial information is extracted to build complete record.
This scheme assumes that data at a given offset in each of the tables involved belong to the same entity
Given this assumption, the join condition may refer to the number of target records as an offset.
More tests can be added if required (eg. the parts in tblStudent
have the same sizes, exactly 1 column in each section is not null, no column contains non-null values in more than 1 section).
Tested on Oracle 12 (should work on all SQL engines though).