SQL Advance Query Help on this Unusual Records

SQL Advance Query Help on this Unusual Records

  

I have a table that look currently like this

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 advance query of its own table or an select advance query (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
To update something like that the best would be to use a cursor.


To select, I suggest something like this (replace x by the number of distinct lines)

SELECT T1.id, T1.SNUM, T2.Name, T3.Grade
FROM
(SELECT id, SNUM, FROM Table WHERE id<=x) T1,
(SELECT id-x, Name FROM Table WHERE id BETWEEN x+1 AND 2*x) T2,
(SELECT id-2*x, Grade FROM Table WHERE id > 2*x) T3
WHERE T1.id=T2.id AND T1.id=T3.id


My logic is to turn
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
Into
ID      SNUM   Name    Grade
1       ST01
2       ST02
3       ST03
4       ST04
1              Simon
2              Kim    
3              Jessica
4              Dale
1                       12
2                       11
3                       11
4                       10
And then join all that by id.

I like your logic but...

The Id column can't be change. The Id exist automatically when you create a new table. Outsystems made it primary key and cannot be duplicated. 
I only changed the id in the selection, made absolutelly no changes to the table.

It looks like this so the advanced query can return with the structure of an existing entity. What you do with id, is up to you. You can simply ignore it in the select above, but for that you must define a new structure to use as query output.
I see... interesting...

I ended up doing this way

SELECT t1.SNUM, t2.NAME, t3.GRADE
FROM(select count(*) cnt from table_name tagg where tagg.SNUM != '') agg
cross join table_name   t1
      join table_name   t2  on ( t2.ID = t1.ID + agg.cnt )
      join table_name   t3  on ( t3.ID = t2.ID + agg.cnt )
     where t1.SNUM != ''
       and t2.NAME != ''
       and t3.GRADE != '';
Looks good and it works. Cool.