Cool Tips‎ > ‎List Manipulation‎ > ‎

List Manipulation - Reorder Items in a List

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        Unknown

Description

Several times over the past 10 years, I've been involved with writing code to manipulate lists.
One notable occasion was for a large Canadian online DVD rental company for which this formed a core part of their web site.
The code written on that occasion was a little less than elegant, but it worked.

I stumbled across this piece of code which was originally for manipulating a todo list and I've tried to genericise it to cope with any enumerated list.

Initially i thought i may be able to make use of the ability to pass a table as a paraemter to an SP, but these table parameters must first be declared as
a user-defined datatype, and are only passable as read-only parameters - pretty useless when the idea is to reorder and return the contents.
Finally I had to settle on retaining the idea used in the original code, which was to utilise a working table that was usable concurrently:

  1. Populate the working table with the list data and a unique identifier (GUID)
  2. Call the SP, passing in the GUID to identify the list data set to be worked on and the reordering parameters
  3. Consume the results once the SP has completed, again using the GUID to identify the list data set
I will only produce 2 pieces of test code - one to populate the list data (with multiple lists)
and the second to test some list reordering.
This will demonstrate that only data from the specified list will be reordered, thus making the SP suitable for concurrent use.


Some assumptions here:
  • The list item is a VARCHAR(MAX) data type - conversion to and from this data type (if required)   will be the responsibility of the calling process
  • The List Item has been included for clarity.  It really is superfluous as the List PK alone is used in the reordering
  • List PK is a single column and is an integer
  • The list order is an integer sequence with no gaps.  Must be one-based  ie 1,2,3,4,5...

I've attempted to make sure that the code can be fairly easily adapted for most list purposes, but I'd appreciate any feedback.

Code

Stored Procedure:


DROP PROCEDURE dbo.usp_ReorderListItems
GO


CREATE PROCEDURE dbo.usp_ReorderListItems
          
(
              
@ListGUID               UNIQUEIDENTIFIER,  -- to allow concurrent use of the list table, this identifies the list to be processed
              
@ListPK                 INT,               -- PK to uniquely identify the item within the specified list that is to be reordered
              
@TargetListSequenceID   INT                -- The new sequence ID that the item is to be changed to
          
)
AS

/**************************************************************************
Purpose:   To change the position of an item in a list, reordering the
           remaimning items in a list.
           This is done using a single update command and without the use
           of a cursor, CTE or loop.

Author:        Unknown
History:   ?? - Intial Issue
           19 Jul 2012 - ChillyDBA - a slight reworking to make it more usable concurrently, plus
            extensive renaming to more generic names (the original code was aimed at a ToDo list)


***************************************************************************/


DECLARE @OldListSequenceID  INT

-- the the id of the item to be updated
-- this becomes the old value that is used in the update code
SELECT @OldListSequenceID = ListSequenceID
FROM ListData_Working
WHERE ListGUID = @ListGUID
AND ListPK = @ListPK

SELECT @OldListSequenceID, @ListGUID, @ListPK

UPDATE lw2
SET lw2.ListSequenceID = ns.NewListSequenceID
FROM ListData_Working lw2
INNER JOIN
(
  
SELECT
      
lw3.ListPK,
      
lw3.ListSequenceID,
      
ROW_NUMBER() OVER
          
(ORDER BY
              
(
                  
CASE
                      
WHEN @TargetListSequenceID > @OldListSequenceID THEN -- moving down as the new sequence number is higher than the old one
                                      
CASE
                                          
WHEN lw3.ListSequenceID = @OldListSequenceID THEN @TargetListSequenceID + 2
                                          
WHEN (lw3.ListSequenceID <= @TargetListSequenceID AND lw3.ListSequenceID <> @OldListSequenceID) THEN lw3.ListSequenceID -- less than new seq so leave alone
                                          
WHEN (lw3.ListSequenceID > @TargetListSequenceID AND lw3.ListSequenceID <> @OldListSequenceID) THEN lw3.ListSequenceID + 2
                                      
END
                       WHEN
@TargetListSequenceID < @OldListSequenceID THEN -- moving up as the new sequence number is less than the old one
                                      
CASE
                                          
WHEN lw3.ListSequenceID = @OldListSequenceID THEN @TargetListSequenceID + 1
                                          
WHEN (lw3.ListSequenceID >= @TargetListSequenceID AND lw3.ListSequenceID <> @OldListSequenceID) THEN lw3.ListSequenceID + 2 -- greater than new seq so leave alone
                                          
WHEN (lw3.ListSequenceID < @TargetListSequenceID AND lw3.ListSequenceID <> @OldListSequenceID) THEN lw3.ListSequenceID
                                      
END
                       ELSE
lw3.ListSequenceID -- not moving (the sequence number is the same as it was before)
                  
END
              
)
           )
AS NewListSequenceID
  
FROM ListData_Working lw3
  
WHERE ListGUID = @ListGUID
) ns
  
ON lw2.ListPK = ns.ListPK
WHERE lw2.ListGUID = @ListGUID


Test Code (Data Initialization):


/**********************************************************
                   Test Code #1
           Create the list to be managed
   -----------------------------------------
**********************************************************/

DROP TABLE ListData_Working
GO

CREATE TABLE ListData_Working
(
  
ListGUID        UNIQUEIDENTIFIER,
  
ListPK          INT,
  
ListSequenceID  INT,
  
ListItem        VARCHAR(MAX)
)

DECLARE @GUID       UNIQUEIDENTIFIER


SELECT
@GUID = 'E62516A7-5C7C-4DA0-9BB9-6E02523AEDAD' --NEWID()

INSERT ListData_Working (ListGUID,   ListPK,         ListSequenceID,     ListItem)
SELECT                  @GUID,      10001,          1,                  'List 0 Item 1'
UNION
SELECT                  
@GUID,      10002,          2,                  'List 0 Item 2'
UNION
SELECT                  
@GUID,      10003,          3,                  'List 0 Item 3'
UNION
SELECT                  
@GUID,      10004,          4,                  'List 0 Item 4'
UNION
SELECT                  
@GUID,      10004,          5,                  'List 0 Item 5'
UNION
SELECT                  
@GUID,      10004,          6,                  'List 0 Item 6'
UNION
SELECT                  
@GUID,      10004,          7,                  'List 0 Item 7'


SELECT @GUID = '9A55F4E1-8AE8-4399-B5DB-B19352688D77' --NEWID()

INSERT ListData_Working (ListGUID,   ListPK,         ListSequenceID,     ListItem)
SELECT                  @GUID,      10001,          1,                  'List 1 Item 1'
UNION
SELECT                  
@GUID,      10002,          2,                  'List 1 Item 2'
UNION
SELECT                  
@GUID,      10003,          3,                  'List 1 Item 3'
UNION
SELECT                  
@GUID,      10004,          4,                  'List 1 Item 4'


SELECT @GUID = '7A30986C-354E-4201-B1BE-CE34FB304C16' --NEWID()

INSERT ListData_Working (ListGUID,   ListPK,         ListSequenceID,     ListItem)
SELECT                  @GUID,      20001,          1,                  'List 2 Item 1'
UNION
SELECT                  
@GUID,      20002,          2,                  'List 2 Item 2'
UNION
SELECT                  
@GUID,      20003,          3,                  'List 2 Item 3'
UNION
SELECT                  
@GUID,      20004,          4,                  'List 2 Item 4'
UNION
SELECT                  
@GUID,      20005,          5,                  'List 2 Item 5'
UNION
SELECT                  
@GUID,      20006,          6,                  'List 2 Item 6'


SELECT @GUID = '0536658B-1677-43AC-ADF5-CEF0A2E30BF1' --NEWID()

INSERT ListData_Working (ListGUID,   ListPK,         ListSequenceID,     ListItem)
SELECT                  @GUID,      30001,          1,                  'List 3 Item 1'
UNION
SELECT                  
@GUID,      30002,          2,                  'List 3 Item 2'
UNION
SELECT                  
@GUID,      30003,          3,                  'List 3 Item 3'
UNION
SELECT                  
@GUID,      30004,          4,                  'List 3 Item 4'
UNION
SELECT                  
@GUID,      30005,          5,                  'List 3 Item 5'


SELECT *
FROM ListData_Working
ORDER BY ListGUID, ListSequenceID


Test Code (List Processing):


/**********************************************************
                   Test Code #2
           Reorder an item upwards
   -----------------------------------------
**********************************************************/


SELECT *
FROM ListData_Working
ORDER BY ListGUID, ListSequenceID


EXEC dbo.usp_ReorderListItems
          
@ListGUID = '0536658B-1677-43AC-ADF5-CEF0A2E30BF1',
          
@ListPK = 30004,
          
@TargetListSequenceID = 2

SELECT *
FROM ListData_Working
ORDER BY ListGUID, ListSequenceID

ċ
Reordering a List.sql
(8k)
Andy Hughes,
Jul 19, 2012, 12:11 PM
Comments