CODEWITHSUNDEEP
sql-server
shallcrass
shallcrass

Reputation: 11

SQL Shift Table Column Down 1

I have a table of +15 million rows and 36 columns, there are two rows of data for every object to which the table refers. I need to:

Move one Column 0 down one space so that the useful information from that column appears in the row below.

Here is a sample of the data with less columns:

Table name = ekd0310

I want to shift Column 0 down 1

Column 0    Column 1    Column 2    Column 3
B02100AA.CZE            
B02100AA.CZF    I   MIGA0027    SUBDIREC.019
B02100AA.CZG            
B02100AA.CZH    I   MIGA0027    SUBDIREC.019
B02100AA.CZI            
B02100AA.CZJ    I   MIGA0027    SUBDIREC.019
B02100AA.CZK            '

Upvotes: 0

Views: 1460

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

THe function that you are looking for is probably lead(). You can use this if you assume that there is a column that specifies the ordering. An example:

select e.*, lead(col) over (order by id) as nextcol
from ekd0310 e;

Although this is an ANSI standard function, not all databases support it (yet). You can do something similar with correlated subqueries. Similarly, the above returns the information, but it is possible to do this as an update as well.

Upvotes: 1

Related Questions