Sql Ordering Hiarchy

Question

I am working on a SQL Statement that I can't seem to figure out. I need to order the results alphabetically, however, I need "children" to come right after their "parent" in the order. Below is a simple example of the table and data I'm working with. All non relevant columns have been removed. I'm using SQL Server 2005. Is there an easy way to do this?

tblCats
=======

idCat  |  fldCatName        | idParent
--------------------------------------
1      |  Some Category     | null
2      |  A Category        | null
3      |  Top Category      | null
4      |  A Sub Cat         | 1
5      |  Sub Cat1          | 1
6      |  Another Cat       | 2
7      |  Last Cat          | 3
8      |  Sub Sub Cat       | 5


Results of Sql Statement:

A Category
 Another Cat
Some Category
 A Sub Cat1
 Sub Cat 1
  Sub Sub Cat
Top Category
 Last Cat

(The prefixed spaces in the result are just to add in understanding of the results, I don't want the prefixed spaces in my sql result. The result only needs to be in this order.)

Answer 1

I'm not aware of any SQL Server (or Ansi-SQL) inherent support for this.

I don't supposed you'd consider a temp table and recursive stored procedure an "easy" way ? J

Answer 2

You can do it with a hierarchical query, as below.

It looks a lot more complicated than it is, due to the lack of a PAD funciton in t-sql. The seed of the hierarchy are the categories without parents. The fourth column we select is their ranking alphabetically (converted to a string and padded). Then we union this with their children. At each recursion, the children will all be at the same level, so we can get their ranking alphabetically without needing to partition. We can concatenate these rankings together down the tree, and order by that.

;WITH Hierarchy AS (
    SELECT 
        idCat, fldCatName, idParent, 
        CAST(RIGHT('00000'+ 
                   CAST(ROW_NUMBER() OVER (ORDER BY fldCatName) AS varchar(8))
                   , 5) 
            AS varchar(256)) AS strPath
    FROM Category
    WHERE idParent IS NULL

    UNION ALL

    SELECT 
        c.idCat, c.fldCatName, c.idParent, 
        CAST(h.strPath + 
            CAST(RIGHT('00000'+ 
                          CAST(ROW_NUMBER() OVER (ORDER BY c.fldCatName) AS varchar(8))
                       , 5) AS varchar(16)) 
            AS varchar(256))
    FROM Hierarchy h
        INNER JOIN Category c ON c.idParent = h.idCat
)
SELECT idCat, fldCatName, idParent, strPath
FROM Hierarchy
ORDER BY strPath

With your data:

idCat  fldCatName      idParent   strPath
------------------------------------------------
  2    A Category        NULL     00001
  6    Another Category  2        0000100001
  1    Some Category     NULL     00002
  4    A Sub Category    1        0000200001
  5    Sub Cat1          1        0000200002
  8    Sub Sub Category  5        000020000200001
  3    Top Category      NULL     00003
  7    Last Category     3        0000300001

Answer 3

Paul's answer is excellent, but I thought I would throw in another idea for you. Joe Celko has a solution for this in his SQL for Smarties book (chapter 29). It involves maintaining a separate table containing the hierarchy info. Inserts, updates, and deletes are a little complicated, but selects are very fast.

Sorry I don't have a link or any code to post, but if you have access to this book, you may find this helpful.

Answer 4

It can be done in CTE... Is this what you're after ?

   With MyCats (CatName, CatId, CatLevel, SortValue)
   As
    ( Select fldCatName CatName, idCat CatId,
         0 Level, Cast(fldCatName As varChar(200)) SortValue
      From tblCats
      Where idParent Is Null
      Union All
      Select c.fldCatName CatName, c.idCat CatID,
         CatLevel + 1 CatLevel,
         Cast(SortValue + '\' + fldCatName as varChar(200)) SortValue
      From tblCats c Join MyCats p 
          On p.idCat = c.idParent)

   Select CatName, CatId, CatLevel, SortValue
   From MyCats
   Order By SortValue

EDIT: (thx to Pauls' comment below) If 200 characters is not enough to hold the longest concatenated string "path", then change the value to as high as is needed... you can make it as high as 8000