SQL Advance Query Help on this Unusual Records

DAVID CHEN

Question

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

04 Apr 2014

Nuno Reis

MVP

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.

04 Apr 2014

DAVID CHEN

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.

04 Apr 2014

Nuno Reis

MVP

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.

04 Apr 2014

DAVID CHEN

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 != '';

05 Apr 2014

Nuno Reis

MVP

Looks good and it works. Cool.

05 Apr 2014

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines