I have a sorting issue in a sql-server 2017 view. To simplify the question: I have a table with hierarchical data and has two columns: key and txt. The key column is used for the hierarchical order and has one, two or three positions. The txt column just has random text values. I need to sort the data, but on a combination of both key and txt columns. To be more precise, I need to get from the left view (sorted on key column) to the right view (the sort I need):

So the view should first show the top level (key is one character) and then below that row the txt values alphabetically (key is two characters). But if the key has three characters, the rows must be placed alphabetically under the matching key with two characters. In the example above, row with key A31 must be listed directly under the row with key A3, row with key B42 must be directly below B4 and B41 below B42, etc.

I have tried many things, but I cannot get the rows with the three character keys to appear directly under the proper two character key rows.

This is an example of what I tried:

SELECT   *
FROM     tbl
ORDER BY CASE LEN(key) WHEN 1 THEN key 
                       WHEN 2 THEN LEFT(key, 1)   '10'
                       ELSE LEFT(key, 1)   '20' 
         END, txt

But this places the rows with three character keys at the bottom of the list... Hope someone can put me in the right direction.

CodePudding user response：

This is a really complicated process because your rules are more complicated than your schema. Here's my attempt, using window functions to group things together and determine which 2-character substring has the lowest txt value, then perform a series of ordering conditionals:

WITH cte AS 
(
  SELECT [key], 
    l  = LEN([key]),
    k1 = LEFT([key],1), 
    k2 = LEFT([key],2), 
    txt 
  FROM dbo.YourTableName
), 
cte2 AS
(
  SELECT *,
    LowestTxt = MIN(CASE WHEN l = 2 THEN txt END) OVER (PARTITION BY k2),
    Len2RN = ROW_NUMBER() OVER (PARTITION BY k2 
             ORDER BY CASE WHEN l = 2 THEN txt ELSE 'zzzzz' END)
  FROM cte
)
  SELECT [key], txt 
  FROM cte2 
  ORDER BY k1,
    CASE WHEN l > 1 THEN 1 END,
    LowestTxt, 
    CASE WHEN l = 2 THEN 'aaa' ELSE txt END, 
    Len2RN;

Example in this working fiddle.