007 007 - 5 months ago 25
SQL Question

T-SQL - Rearrange a list of values in table

Code:

CREATE TABLE #Temp (T_ID INT IDENTITY(1,1), T_Desc NVARCHAR(10), PriorityOrder INT)

INSERT INTO #Temp
SELECT 'Apple',1
UNION
SELECT 'LG',2
UNION
SELECT 'Microsoft',3
UNION
SELECT 'Samsung',4
UNION
SELECT 'Sony',5

SELECT * FROM #Temp

DROP TABLE #Temp


Output:

T_ID T_Desc PriorityOrder
1 Apple 1
2 LG 2
3 Microsoft 3
4 Samsung 4
5 Sony 5


Goal:
To rearrange the PriortyOrder if a T_Desc value (i.e. parameter #1) were to shift up/down one or more rows.
i.e. If LG shifted from PriortyOrder 2 to 5 (i.e. parameter #2), the new output would look like,

T_ID T_Desc PriorityOrder
1 Apple 1
2 LG 5
3 Microsoft 2
4 Samsung 3
5 Sony 4


In other words, everything below LG shifted up for "Priortyorder" field. Also, this should work if Sony were to move from PriorityOrder 5 to 1 (moving up in the order).

T_ID T_Desc PriorityOrder
1 Apple 2
2 LG 3
3 Microsoft 4
4 Samsung 5
5 Sony 1


I've seen examples of moving/shifting/re-sequencing order one row up/down but haven't been able to figure out shifting over multiple rows.

UPDATE: The list can vary in the amount of data.

Answer
Declare @srcID, @srcPri int, @dstPri int, @minPri int, @maxPri int, @delta

-- set @srcID and @dstPri as appropriate

Begin Transaction

Select @srcPri=PriorityOrder from #temp with (HOLDLOCK) where T_ID=@srcID

If @srcPri<@dstPri begin
  Select @minPri=@srcPri+1,
         @maxPri=@dstPri,
         @delta=-1
End else begin
  Select @minPri=@dstPri,
         @maxPri=@srcPri-1,
         @delta=1
End

Update #temp
Set PriorityOrder=PriorityOrder+@delta
Where PriorityOrder between @minPri and @maxPri

Update #temp
Set PriorityOrder=@dstPri
Where T_ID=@srcID

Commit Transaction

You need to provide the T_ID of the row that is to be moved/shifted in @srcID and the target PriorityOrder in @dstPri. If you run multiple instances of the above code concurrently, it is possible to get a deadlock if both instances need to update the same records