Parent hierarchy of a child with order by name

Question

I want to create a SQL Server query for the following. I have data like:

Id       Name       parentId
1         STU        0
2         XYZ        5
3         PQR        5
4         EFG        0
5         ABC        0
6         HIJ        1
7         DEF        1

Here is the query I'm using:

SELECT *
FROM TABLE
ORDER BY CASE WHEN parentId = 0 THEN id ELSE parentId END ASC, id ASC

Output (parent and its children are sorted together):

Id       Name       parentId
1         STU        0
6         HIJ        1
7         DEF        1
4         EFG        0
5         ABC        0
2         XYZ        5
3         PQR        5

Now I also need to sort by name, first by parent name, and then by all child elements, also sorted by name. Expected Result:

Id       Name       parentId
5         ABC        0
3         PQR        5
2         XYZ        5
4         EFG        0
1         STU        0
7         DEF        1
6         HIJ        1

Does anyone have a solution for this? To do this, I need a strict SQL Server query.

PS There is only one level of hierarchy.

Answer 1

SQL Server cte. , , , udf, , .
, .
1, 11, 2, 13, 21, 3 ,
1, 2, 3, 11, 13, 21.
, ,
1, 11, 13, 2, 21, 3.

, - !
-, ( ):

DECLARE @T AS TABLE
(
    Id int,
    [Name] char(3),
    parentId int
)
INSERT INTO @T (Id, [Name], parentId) VALUES
(1, 'STU', 0),
(2, 'XYZ', 5),
(3, 'PQR', 5),
(4, 'EFG', 0),
(5, 'ABC', 0),
(6, 'HIJ', 1),
(7, 'DEF', 1),
(8, 'AAA', 3),
(9, 'ZZZ', 3)

. , .

Cte:

;WITH CTE AS
(
    SELECT  Id, 
            [Name], 
            ParentId, 
            -- Row_Number returns a bigint - max value have 19 digits
            CAST(ROW_NUMBER() OVER(ORDER BY [Name]) as varchar(19)) As Sort
    FROM @T 
    WHERE parentId = 0

    UNION ALL

    SELECT  T.Id, 
            T.[Name], 
            T.ParentId,
            CAST(Sort + CAST(ROW_NUMBER() OVER(ORDER BY T.[Name]) as varchar(19)) as varchar(19))
    FROM @T T
    JOIN CTE ON T.parentId = CTE.Id 
)

:

SELECT Id, [Name], ParentId
FROM CTE 
ORDER BY Sort -- alphabetic sort will order 11 before 2...

:

Id  Name    ParentId
5   ABC     0
3   PQR     5
8   AAA     3
9   ZZZ     3
2   XYZ     5
4   EFG     0
1   STU     0
7   DEF     1
6   HIJ     1

Answer 2

;WITH CTE
AS
(
   SELECT
  RN =0,   
  ID,
      NAME,
     parentId = ID
  FROM T1
    WHERE parentId = 0

  UNION ALL

  SELECT
  RN = ROW_NUMBER() OVER(PARTITION BY T1.parentId ORDER BY T1.name asc),
     T1.ID,
      T1.NAME,
  T1.parentId
  FROM T1
   INNER JOIN T1 T2
      ON T1.parentId =T2.ID

)
SELECT
id,
name,
parentid
FROM CTE
ORDER BY parentId DESC,RN

Answer 3

, :

SELECT
    category.*,
    -- bring parent and its children together
    CASE WHEN parent.Id IS NULL THEN category.Name ELSE parent.Name END AS sort1,
    -- move parent to top followed by its children
    CASE WHEN parent.Id IS NULL THEN NULL ELSE category.Name END AS sort2
FROM category
LEFT JOIN category AS parent ON category.parentId = parent.Id
ORDER BY sort1, sort2

:

+------+------+----------+-------+-------+
| Id   | Name | parentId | sort1 | sort2 |
+------+------+----------+-------+-------+
|    5 | ABC  |        0 | ABC   | NULL  |
|    3 | PQR  |        5 | ABC   | PQR   |
|    2 | XYZ  |        5 | ABC   | XYZ   |
|    4 | EFG  |        0 | EFG   | NULL  |
|    1 | STU  |        0 | STU   | NULL  |
|    7 | DEF  |        1 | STU   | DEF   |
|    6 | HIJ  |        1 | STU   | HIJ   |
+------+------+----------+-------+-------+

, SELECT, , .

Answer 4

Sql, . 2, , :

declare @tempT table(ID int, name varchar(3), parentID int, sortLevel1 int, sortlevel2 int)

insert into @tempT
select t1.ID,t1.name,t1.parentID,RowNumber() Over(order by (select null)),-1
from table t1  
where parentId=0
order by t1.name 

insert into @tempT
select t2.ID,t2.name,t2.parentID,t1.sortLevel1,RowNumber() Over(order by (select null))
from table t1  
join @tempT t2 on t1.id=t2.parentID
order by t2.name

select * from @temp order by t1.sortLevel1, sortLevel2