How to code Microsoft Excel "Shift Cells Up" feature in SQL

Question

Take a simple table like below:

Column Headings: || Agent's Name || Time Logged In || Center ||  
Row 1: Andrew  || 12:30 PM || Home Base  
Row 2: Jeff || 7:00 AM || Virtual Base  
Row 3: Ryan || 6:30 PM || Test Base

Now lets say that a single cell is deleted so the table now looks like this:

Column Headings: || Agent's Name || Time Logged In || Center ||  
Row 1: Andrew  || 12:30 PM ||   
Row 2: Jeff || 7:00 AM || Virtual Base  
Row 3: Ryan || 6:30 PM || Test Base

Notice that "Home Base" is missing. Now in excel you can delete the cell and shift the rest so the finished product looks like below:

Column Headings: || Agent's Name || Time Logged In || Center ||  
Row 1: Andrew  || 12:30 PM || Virtual Base   
Row 2: Jeff || 7:00 AM || Test Base  
Row 3: Ryan || 6:30 PM || 

And you can see we are left with a blank cell last row.

How do I code this procedure of shifting the cells up in SQL?

I've been struggling on this problem for weeks! Thank you!

Answer 1

You can't - SQL tables aren't Excel sheets - they just don't have that kind of a structure. No matter how hard you try - you won't be able to do something like that. It's just fundamentally different.

SQL Server tables have rows and columns - sure - but they have no implied order or anything. You cannot "shift" a row up - there's no "up" per se - it all depends on your ordering.

It's worse than comparing apples to oranges - it's like comparing apples to granite blocks - it's just not the same - don't waste your time trying to make it the same.

Answer 2

One of many options is to use an outer apply to fetch the Center from the next row:

declare @t table (name varchar(50), login time, center varchar(50))

insert into @t (name, login, center)
select 'Andrew', '12:30 PM', 'Home Base'
union all select 'Jeff', '7:00 AM', 'Virtual Base'
union all select 'Ryan', '6:30 PM', 'Test Base'

update t1
set t1.center = t3.center
from @t t1
outer apply (
    select top 1 t2.center
    from @t t2
    where t2.name > t1.name
    order by t2.name
) t3

select * from @t

You do have to specify an ordering (the example orders on name.)

Answer 3

If you have two sets and you simply want to assign one of each in the second set to items from the first set, "using them up", the simplest thing is to use ROW_NUMBER() on both sets and do a LEFT JOIN on the ROW_NUMBER() column from the first to the second set, assigning each row the next available item:

WITH set1 AS (
    SELECT *, ROW_NUMBER() (OVER ORDER BY set1.sortorder /* choice of order by is obviously important */) AS ROWNUM
    FROM set1
)
,set2 AS (
    SELECT *, ROW_NUMBER() (OVER ORDER BY set2.sortorder /* choice of order by is obviously important */) AS ROWNUM
    FROM set2
)
SELECT *
FROM set1
LEFT JOIN set2
    ON set1.keys = set2.keys -- if there are any keys
    AND set1.ROW_NUM = set2.ROW_NUM