SQL - Is this possible?

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 th开发者_如何学Pythonat SQL does not actually extract ables row by row, so is this possible?

Thanks ahead of time!

---

You can do this in a stored procedure using cursors.

---

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.

---

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.

---

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