[SOLVED] SQL Update Query on this Unusual Records

SQL Update Query on this Unusual Records

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?

Solution

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).