sqlsql-server

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