SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE INVENTORYSHELFLOCATIONS (
  "SHELFLOCID" INTEGER,
  "PARENTSHELFLOCID" INTEGER,  
  "DEPTH" INTEGER,
  "PICKSORTVALUE" INTEGER,
  "PICKORDER" INTEGER
);

INSERT INTO INVENTORYSHELFLOCATIONS
  ("SHELFLOCID", "PARENTSHELFLOCID", "DEPTH", "PICKSORTVALUE")
VALUES
  ('55', '0', '1', '1'),
  ('33', '55', '2', '1'),
  ('88', '55', '2', '2'),
  ('11', '0', '1', '2'),
  ('77', '11', '2', '1');
5 rows affected

SELECT
    LEVEL1_DATA.PICKSORTVALUE AS LEVEL1SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL2SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL3SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL4SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL5SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL6SORT,
    LEVEL1_DATA.SHELFLOCID
FROM
    INVENTORYSHELFLOCATIONS LEVEL1_DATA
WHERE
    LEVEL1_DATA.DEPTH = 1
  
UNION

SELECT
    LEVEL2_LEVEL1.PICKSORTVALUE AS LEVEL1SORT,
    LEVEL2_DATA.PICKSORTVALUE AS LEVEL2SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL3SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL4SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL5SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL6SORT,
    LEVEL2_DATA.SHELFLOCID
FROM
    INVENTORYSHELFLOCATIONS LEVEL2_DATA
JOIN
    INVENTORYSHELFLOCATIONS LEVEL2_LEVEL1 ON LEVEL2_LEVEL1.SHELFLOCID = LEVEL2_DATA.PARENTSHELFLOCID
WHERE
    LEVEL2_DATA.DEPTH = 2

UNION

SELECT
    LEVEL3_LEVEL1.PICKSORTVALUE AS LEVEL1SORT,
    LEVEL3_LEVEL2.PICKSORTVALUE AS LEVEL2SORT,
    LEVEL3_DATA.PICKSORTVALUE AS LEVEL3SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL4SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL5SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL6SORT,
    LEVEL3_DATA.SHELFLOCID
FROM
    INVENTORYSHELFLOCATIONS LEVEL3_DATA
JOIN
    INVENTORYSHELFLOCATIONS LEVEL3_LEVEL2 ON LEVEL3_LEVEL2.SHELFLOCID = LEVEL3_DATA.PARENTSHELFLOCID
JOIN
    INVENTORYSHELFLOCATIONS LEVEL3_LEVEL1 ON LEVEL3_LEVEL1.SHELFLOCID = LEVEL3_LEVEL2.PARENTSHELFLOCID
WHERE
    LEVEL3_DATA.DEPTH = 3

UNION

SELECT
    LEVEL4_LEVEL1.PICKSORTVALUE AS LEVEL1SORT,
    LEVEL4_LEVEL2.PICKSORTVALUE AS LEVEL2SORT,
    LEVEL4_LEVEL3.PICKSORTVALUE AS LEVEL3SORT,
    LEVEL4_DATA.PICKSORTVALUE AS LEVEL4SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL5SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL6SORT,
    LEVEL4_DATA.SHELFLOCID
FROM
    INVENTORYSHELFLOCATIONS LEVEL4_DATA
JOIN
    INVENTORYSHELFLOCATIONS LEVEL4_LEVEL3 ON LEVEL4_LEVEL3.SHELFLOCID = LEVEL4_DATA.PARENTSHELFLOCID
JOIN
    INVENTORYSHELFLOCATIONS LEVEL4_LEVEL2 ON LEVEL4_LEVEL2.SHELFLOCID = LEVEL4_LEVEL3.PARENTSHELFLOCID
JOIN
    INVENTORYSHELFLOCATIONS LEVEL4_LEVEL1 ON LEVEL4_LEVEL1.SHELFLOCID = LEVEL4_LEVEL2.PARENTSHELFLOCID
WHERE
    LEVEL4_DATA.DEPTH = 4

UNION

SELECT
    LEVEL5_LEVEL1.PICKSORTVALUE AS LEVEL1SORT,
    LEVEL5_LEVEL2.PICKSORTVALUE AS LEVEL2SORT,
    LEVEL5_LEVEL3.PICKSORTVALUE AS LEVEL3SORT,
    LEVEL5_LEVEL4.PICKSORTVALUE AS LEVEL4SORT,
    LEVEL5_DATA.PICKSORTVALUE AS LEVEL5SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL6SORT,
    LEVEL5_DATA.SHELFLOCID
FROM
    INVENTORYSHELFLOCATIONS LEVEL5_DATA
JOIN
    INVENTORYSHELFLOCATIONS LEVEL5_LEVEL4 ON LEVEL5_LEVEL4.SHELFLOCID = LEVEL5_DATA.PARENTSHELFLOCID
JOIN
    INVENTORYSHELFLOCATIONS LEVEL5_LEVEL3 ON LEVEL5_LEVEL3.SHELFLOCID = LEVEL5_LEVEL4.PARENTSHELFLOCID
JOIN
    INVENTORYSHELFLOCATIONS LEVEL5_LEVEL2 ON LEVEL5_LEVEL2.SHELFLOCID = LEVEL5_LEVEL3.PARENTSHELFLOCID
JOIN
    INVENTORYSHELFLOCATIONS LEVEL5_LEVEL1 ON LEVEL5_LEVEL1.SHELFLOCID = LEVEL5_LEVEL2.PARENTSHELFLOCID
WHERE
    LEVEL5_DATA.DEPTH = 5

UNION

SELECT
    LEVEL6_LEVEL1.PICKSORTVALUE AS LEVEL1SORT,
    LEVEL6_LEVEL2.PICKSORTVALUE AS LEVEL2SORT,
    LEVEL6_LEVEL3.PICKSORTVALUE AS LEVEL3SORT,
    LEVEL6_LEVEL4.PICKSORTVALUE AS LEVEL4SORT,
    LEVEL6_LEVEL5.PICKSORTVALUE AS LEVEL5SORT,
    LEVEL6_DATA.PICKSORTVALUE AS LEVEL6SORT,
    LEVEL6_DATA.SHELFLOCID
FROM
    INVENTORYSHELFLOCATIONS LEVEL6_DATA
JOIN
    INVENTORYSHELFLOCATIONS LEVEL6_LEVEL5 ON LEVEL6_LEVEL5.SHELFLOCID = LEVEL6_DATA.PARENTSHELFLOCID
JOIN
    INVENTORYSHELFLOCATIONS LEVEL6_LEVEL4 ON LEVEL6_LEVEL4.SHELFLOCID = LEVEL6_LEVEL5.PARENTSHELFLOCID
JOIN
    INVENTORYSHELFLOCATIONS LEVEL6_LEVEL3 ON LEVEL6_LEVEL3.SHELFLOCID = LEVEL6_LEVEL4.PARENTSHELFLOCID
JOIN
    INVENTORYSHELFLOCATIONS LEVEL6_LEVEL2 ON LEVEL6_LEVEL2.SHELFLOCID = LEVEL6_LEVEL3.PARENTSHELFLOCID
JOIN
    INVENTORYSHELFLOCATIONS LEVEL6_LEVEL1 ON LEVEL6_LEVEL1.SHELFLOCID = LEVEL6_LEVEL2.PARENTSHELFLOCID
WHERE
    LEVEL6_DATA.DEPTH = 6

  ORDER BY
    1, 2, 3, 4, 5, 6


LEVEL1SORT
LEVEL2SORT
LEVEL3SORT
LEVEL4SORT
LEVEL5SORT
LEVEL6SORT
SHELFLOCID


1
0
0
0
0
0
55

1
1
0
0
0
0
33

1
2
0
0
0
0
88

2
0
0
0
0
0
11

2
1
0
0
0
0
77


WITH SortedData AS (
    SELECT
        SHELFLOCID,
        ROW_NUMBER() OVER (
            ORDER BY LEVEL1SORT, LEVEL2SORT, LEVEL3SORT, LEVEL4SORT, LEVEL5SORT, LEVEL6SORT
        ) AS RowNum
    FROM (

  SELECT
  LEVEL1_DATA.PICKSORTVALUE AS LEVEL1SORT,
  CAST(0 AS NUMERIC(10,0)) AS LEVEL2SORT,
  CAST(0 AS NUMERIC(10,0)) AS LEVEL3SORT,
  CAST(0 AS NUMERIC(10,0)) AS LEVEL4SORT,
  CAST(0 AS NUMERIC(10,0)) AS LEVEL5SORT,
  CAST(0 AS NUMERIC(10,0)) AS LEVEL6SORT,
  LEVEL1_DATA.SHELFLOCID
FROM
  INVENTORYSHELFLOCATIONS LEVEL1_DATA
WHERE
  LEVEL1_DATA.DEPTH = 1


UNION

SELECT
  LEVEL2_LEVEL1.PICKSORTVALUE AS LEVEL1SORT,
  LEVEL2_DATA.PICKSORTVALUE AS LEVEL2SORT,
  CAST(0 AS NUMERIC(10,0)) AS LEVEL3SORT,
  CAST(0 AS NUMERIC(10,0)) AS LEVEL4SORT,
  CAST(0 AS NUMERIC(10,0)) AS LEVEL5SORT,
  CAST(0 AS NUMERIC(10,0)) AS LEVEL6SORT,
  LEVEL2_DATA.SHELFLOCID
FROM
  INVENTORYSHELFLOCATIONS LEVEL2_DATA
LEFT JOIN
  INVENTORYSHELFLOCATIONS LEVEL2_LEVEL1 ON
  LEVEL2_LEVEL1.SHELFLOCID = LEVEL2_DATA.PARENTSHELFLOCID
WHERE
  LEVEL2_DATA.DEPTH = 2


UNION

SELECT
  LEVEL3_LEVEL1.PICKSORTVALUE AS LEVEL1SORT,
  LEVEL3_LEVEL2.PICKSORTVALUE AS LEVEL2SORT,
  LEVEL3_DATA.PICKSORTVALUE AS LEVEL3SORT,
  CAST(0 AS NUMERIC(10,0)) AS LEVEL4SORT,
  CAST(0 AS NUMERIC(10,0)) AS LEVEL5SORT,
  CAST(0 AS NUMERIC(10,0)) AS LEVEL6SORT,
  LEVEL3_DATA.SHELFLOCID
FROM
  INVENTORYSHELFLOCATIONS LEVEL3_DATA
LEFT JOIN
  INVENTORYSHELFLOCATIONS LEVEL3_LEVEL2 ON
  LEVEL3_LEVEL2.SHELFLOCID = LEVEL3_DATA.PARENTSHELFLOCID
LEFT JOIN
  INVENTORYSHELFLOCATIONS LEVEL3_LEVEL1 ON
  LEVEL3_LEVEL1.SHELFLOCID = LEVEL3_LEVEL2.PARENTSHELFLOCID
WHERE
  LEVEL3_DATA.DEPTH = 3


UNION

SELECT
  LEVEL4_LEVEL1.PICKSORTVALUE AS LEVEL1SORT,
  LEVEL4_LEVEL2.PICKSORTVALUE AS LEVEL2SORT,
  LEVEL4_LEVEL3.PICKSORTVALUE AS LEVEL3SORT,
  LEVEL4_DATA.PICKSORTVALUE AS LEVEL4SORT,
  CAST(0 AS NUMERIC(10,0)) AS LEVEL5SORT,
  CAST(0 AS NUMERIC(10,0)) AS LEVEL6SORT,
  LEVEL4_DATA.SHELFLOCID
FROM
  INVENTORYSHELFLOCATIONS LEVEL4_DATA
LEFT JOIN
  INVENTORYSHELFLOCATIONS LEVEL4_LEVEL3 ON
  LEVEL4_LEVEL3.SHELFLOCID = LEVEL4_DATA.PARENTSHELFLOCID
LEFT JOIN
  INVENTORYSHELFLOCATIONS LEVEL4_LEVEL2 ON
  LEVEL4_LEVEL2.SHELFLOCID = LEVEL4_LEVEL3.PARENTSHELFLOCID
LEFT JOIN
  INVENTORYSHELFLOCATIONS LEVEL4_LEVEL1 ON
  LEVEL4_LEVEL1.SHELFLOCID = LEVEL4_LEVEL2.PARENTSHELFLOCID
WHERE
  LEVEL4_DATA.DEPTH = 4


UNION

SELECT
  LEVEL5_LEVEL1.PICKSORTVALUE AS LEVEL1SORT,
  LEVEL5_LEVEL2.PICKSORTVALUE AS LEVEL2SORT,
  LEVEL5_LEVEL3.PICKSORTVALUE AS LEVEL3SORT,
  LEVEL5_LEVEL4.PICKSORTVALUE AS LEVEL4SORT,
  LEVEL5_DATA.PICKSORTVALUE AS LEVEL5SORT,
  CAST(0 AS NUMERIC(10,0)) AS LEVEL6SORT,
  LEVEL5_DATA.SHELFLOCID
FROM
  INVENTORYSHELFLOCATIONS LEVEL5_DATA
LEFT JOIN
  INVENTORYSHELFLOCATIONS LEVEL5_LEVEL4 ON
  LEVEL5_LEVEL4.SHELFLOCID = LEVEL5_DATA.PARENTSHELFLOCID
LEFT JOIN
  INVENTORYSHELFLOCATIONS LEVEL5_LEVEL3 ON
  LEVEL5_LEVEL3.SHELFLOCID = LEVEL5_LEVEL4.PARENTSHELFLOCID
LEFT JOIN
  INVENTORYSHELFLOCATIONS LEVEL5_LEVEL2 ON
  LEVEL5_LEVEL2.SHELFLOCID = LEVEL5_LEVEL3.PARENTSHELFLOCID
LEFT JOIN
  INVENTORYSHELFLOCATIONS LEVEL5_LEVEL1 ON
  LEVEL5_LEVEL1.SHELFLOCID = LEVEL5_LEVEL2.PARENTSHELFLOCID
WHERE
  LEVEL5_DATA.DEPTH = 5


UNION

SELECT
  LEVEL6_LEVEL1.PICKSORTVALUE AS LEVEL1SORT,
  LEVEL6_LEVEL2.PICKSORTVALUE AS LEVEL2SORT,
  LEVEL6_LEVEL3.PICKSORTVALUE AS LEVEL3SORT,
  LEVEL6_LEVEL4.PICKSORTVALUE AS LEVEL4SORT,
  LEVEL6_LEVEL5.PICKSORTVALUE AS LEVEL5SORT,
  LEVEL6_DATA.PICKSORTVALUE AS LEVEL6SORT,
  LEVEL6_DATA.SHELFLOCID
FROM
  INVENTORYSHELFLOCATIONS LEVEL6_DATA
LEFT JOIN
  INVENTORYSHELFLOCATIONS LEVEL6_LEVEL5 ON
  LEVEL6_LEVEL5.SHELFLOCID = LEVEL6_DATA.PARENTSHELFLOCID
LEFT JOIN
  INVENTORYSHELFLOCATIONS LEVEL6_LEVEL4 ON
  LEVEL6_LEVEL4.SHELFLOCID = LEVEL6_LEVEL5.PARENTSHELFLOCID
LEFT JOIN
  INVENTORYSHELFLOCATIONS LEVEL6_LEVEL3 ON
  LEVEL6_LEVEL3.SHELFLOCID = LEVEL6_LEVEL4.PARENTSHELFLOCID
LEFT JOIN
  INVENTORYSHELFLOCATIONS LEVEL6_LEVEL2 ON
  LEVEL6_LEVEL2.SHELFLOCID = LEVEL6_LEVEL3.PARENTSHELFLOCID
LEFT JOIN
  INVENTORYSHELFLOCATIONS LEVEL6_LEVEL1 ON
  LEVEL6_LEVEL1.SHELFLOCID = LEVEL6_LEVEL2.PARENTSHELFLOCID
WHERE
  LEVEL6_DATA.DEPTH = 6

  
    ) AS CombinedData
)
UPDATE INVENTORYSHELFLOCATIONS 
SET INVENTORYSHELFLOCATIONS.PICKORDER = SD.RowNum
FROM SortedData AS SD
WHERE INVENTORYSHELFLOCATIONS.SHELFLOCID = SD.SHELFLOCID; 
5 rows affected

SELECT
  * 
FROM 
  INVENTORYSHELFLOCATIONS
ORDER BY
  PICKORDER


SHELFLOCID
PARENTSHELFLOCID
DEPTH
PICKSORTVALUE
PICKORDER


55
0
1
1
1

33
55
2
1
2

88
55
2
2
3

11
0
1
2
4

77
11
2
1
5