Reorder list by column

Question

I have a column "SortIndex" in a table, and I would like to add a "case update" to reorder that table by passing in ID and the new position of the chosen one.

SQL Fiddle

DECLARE @T TABLE
(
    ID int,
    Name char(1),
    SortIndex int
)

INSERT @T 
SELECT 21, 'A', 1 UNION ALL
SELECT 23, 'B', 2 UNION ALL
SELECT 35, 'C', 3 UNION ALL
SELECT 45, 'D', 4 UNION ALL
SELECT 55, 'E', 5

SELECT * FROM @T    

DECLARE @SortIndex int

/* MOVE A -> 4 */

SET @SortIndex = (SELECT SortIndex FROM @T WHERE ID = 23)

/*
UPDATE @T
    SET 
        SortIndex = CASE ... ??
*/      

/* MOVE D -> 2 */

SELECT * FROM @T

Is this possible to solve this by only using the case statement to rebuild the sortindex'es?

UPDATE

Desired results

A -> 4

    Name SortIndex
    B     1 
    C     2
    D     3
    A     4
    E     5

    D -> 2

    A     1 
    D     2
    B     3
    C     4
    E     5

Farfarak · Accepted Answer · 2012-11-07T08:38:55.927

Use following code to move and rebuild indexes, it adds element after desired elements and shifts others (answer based on the comment clarifications ) :

This is slimmer version:

 DECLARE @SortIndex int,@MoveTo int, @CurrentSortIndex int

SET @CurrentSortIndex  = (SELECT SortIndex FROM @T WHERE ID = 45) --D
SET @MoveTo = 2

UPDATE--D
  @T
SET
  SortIndex = @MoveTo
WHERE
  ID = 45

UPDATE
  @T
SET
  SortIndex = SortIndex - ((@MoveTo-@CurrentSortIndex)/(ABS(@MoveTo-@CurrentSortIndex)))
WHERE
(
    SortIndex BETWEEN @CurrentSortIndex AND @MoveTo
 OR
    SortIndex BETWEEN @MoveTo AND @CurrentSortIndex  
)
AND
  ID != 45

Full example of moving A to position 2, also sqlfidle link http://sqlfiddle.com/#!3/d41d8/5918:

DECLARE @T TABLE
(
    ID int,
    Name char(1),
    SortIndex int
)

INSERT @T 
SELECT 21, 'A', 1 UNION ALL
SELECT 23, 'B', 2 UNION ALL
SELECT 35, 'C', 3 UNION ALL
SELECT 45, 'D', 4 UNION ALL
SELECT 55, 'E', 5


DECLARE @SortIndex int,@MoveTo int, @CurrentSortIndex int
DECLARE @IDToMove INT 
SET @IDToMove = 21--A

SET @CurrentSortIndex  = (SELECT SortIndex FROM @T WHERE ID = @IDToMove) 
SET @MoveTo = 2

UPDATE
  @T
SET
  SortIndex = @MoveTo
WHERE
  ID = @IDToMove

UPDATE
  @T
SET
  SortIndex = SortIndex - ((@MoveTo-@CurrentSortIndex)/(ABS(@MoveTo-@CurrentSortIndex)))
WHERE
(
    SortIndex BETWEEN @CurrentSortIndex AND @MoveTo
 OR
    SortIndex BETWEEN @MoveTo AND @CurrentSortIndex  
)
AND
  ID != @IDToMove

SELECT * FROM @T ORDER BY SortIndex

Initial version:

DECLARE @SortIndex int,@MoveTo int, @CurrentSortIndex int

SET @CurrentSortIndex  = (SELECT SortIndex FROM @T WHERE ID = 21) --A
SET @MoveTo = 2

UPDATE--A
  @T
SET
  SortIndex = @MoveTo
WHERE
  ID = 21

IF @MoveTo > @CurrentSortIndex

UPDATE
  @T
SET
  SortIndex = SortIndex - 1
WHERE
  SortIndex BETWEEN @CurrentSortIndex AND @MoveTo
AND
  ID != 21

 IF @MoveTo < @CurrentSortIndex

UPDATE
  @T
SET
  SortIndex = SortIndex + 1
WHERE
  SortIndex BETWEEN @MoveTo AND @CurrentSortIndex
AND
  ID != 21

its pretty close, not sure what's missing, A and B is 1 for some reason — Lasse Edsvik, Nov 07 '12 at 07:29
I have added example of moving A to 2 in the answer. http://sqlfiddle.com/#!3/d41d8/5918 — Farfarak, Nov 07 '12 at 08:39

score 0 · Answer 2 · answered Nov 06 '12 at 15:27

0

UPDATE @T
SET SortIndex = CASE Name 
        when 'A' then 4 
        when 'D' then 2 
        else Name 
    end

answered Nov 06 '12 at 15:27

D'Arcy Rittich

167,292
40
290
283

That only switches places :) I need to move one, and "push" the others +1 somehow – Lasse Edsvik Nov 06 '12 at 15:32
@LasseEdsvik - Please update your question with what is the form you want your data to look like after the update. – Mahmoud Gamal Nov 06 '12 at 15:36
1

@LasseEdsvik That is not represented in your question. I based my answer on what I could glean from your comments. – D'Arcy Rittich Nov 06 '12 at 15:48

Reorder list by column

2 Answers2