SQL - Is this possible?

Question

I have a table that looks like this:

--------------------------------------------
|   Date   |   House#   |   Subscription   |
--------------------------------------------
|  3/02/10 |   x        |      Monthly     |
--------------------------------------------
|  3/03/10 |   y        |      Weekly      |
--------------------------------------------
|  3/04/10 |   z        |      Daily       |
--------------------------------------------

I need a command that will take a column name and an int and shift the values in those columns up so many levels. So (house, 1) would put z where y is, y where x is, and z would go to 0/Null. Whereas (house, 2) would put z where x is and y and z would go to 0/null.

I understand that SQL does not actually extract ables row by row, so is this possible?

Thanks ahead of time!

Answer 1

You can do this in a stored procedure using cursors.

Answer 2

You can use PL/SQL routine .Take the column name and number as input and then implement the logic as you want. Cursors as suggested above is one of the options that you have.

Answer 3

I would think adding a column that contains a value to use as a sort order you could then update that column as needed and then ordered by that column. If it is not possible to change that table perhaps you could create a new table to hold the sort column and join the two

Answer 4

You should use PL/SQL, here is an example (not for this particular example):

DECLARE 
    CURSOR cpaises 
    IS
    SELECT CO_PAIS, DESCRIPCION, CONTINENTE 
    FROM  PAISES;

    co_pais VARCHAR2(3);
    descripcion VARCHAR2(50);
    continente VARCHAR2(25);
BEGIN
    OPEN cpaises;
    LOOP
        FETCH cpaises INTO co_pais,descripcion,continente;
        EXIT WHEN cpaises%NOTFOUND;
        dbms_output.put_line(descripcion);
    END LOOP; 
    CLOSE cpaises;
END;

I think you could use a variable to indicate which column to select and to update, and inside a loop, you can have an array, with the last n values.