vendredi 24 juin 2016

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.

Aucun commentaire:

Enregistrer un commentaire