CODEWITHSUNDEEP
sqlsql-server-2005row
user416516
user416516

Reputation: 137

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 that SQL does not actually extract ables row by row, so is this possible?

Thanks ahead of time!

Upvotes: 0

Views: 113

Answers (4)

Vaibhav
Vaibhav

Reputation: 5947

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.

Upvotes: 1

greuze
greuze

Reputation: 4398

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.

Upvotes: 1

Roadie57
Roadie57

Reputation: 324

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

Upvotes: 0

mikerobi
mikerobi

Reputation: 20878

You can do this in a stored procedure using cursors.

Upvotes: 2

Related Questions