sql server - Order By SQL Query based on Value another Column

Question

I have table like this:

And I want to short the data based on ParentID that refer to ID. So, if the data with ParentID 5 it will be under the data with ID 5. So this is the expected data that I want:

Is it possible to do that? If its possible, how to order that data? Thank you in advance.

Answer 1

You can use a common table expression (CTE) to first query the parent records, and then query the children records in the second query. If you create a sort column, you can then order the data perfectly:

create table #temp (
  id int,
  name varchar(10),
  [date] date,
  parentid int
)

insert into #temp values
(1, 'AAA', '9/7/2020', 1),
(2, 'BBB', '9/8/2020', 2),
(3, 'CCC', '9/8/2020', 3),
(4, 'DDD', '9/8/2020', 4),
(5, 'EEE', '9/8/2020', 2),
(6, 'FFF', '9/8/2020', 1),
(7, 'GGG', '9/8/2020', 5),
(8, 'HHH', '9/8/2020', 3),
(9, 'III', '9/8/2020', 4),
(10, 'JJJ', '9/8/2020', 10)

;with cte (id, parentid, name, [date], sort) as
(
  /* query #1 : pull only parent records */
  select id, parentid, name, [date],
         cast(right('0000' + cast(row_number() over (order by id) as varchar(5)), 5) as varchar(1024))
  from   #temp
  where  id = parentid                           /* pull just the parent records */

  union all

  /* query #2 : add children records */
  select t.id, t.parentid, t.name, t.[date],
         cast(c.sort + right('0000' + cast(row_number() over (order by t.id) as varchar(5)), 5) as varchar(1024))
  from   cte c                                   /* include data from 1st query */
         inner join #temp t on c.id = t.parentid /* only pull children of the parent records returned in query #1 */
  where  t.id <> t.parentid                      /* a record cannot be a child of itself, prevents infinite recursion */
)
select *
from   cte
order by sort

drop table #temp

Which returns this dataset:

id          parentid    name       date       sort
----------- ----------- ---------- ---------- ----------------
1           1           AAA        2020-09-07 00001
6           1           FFF        2020-09-08 0000100001
2           2           BBB        2020-09-08 00002
5           2           EEE        2020-09-08 0000200001
7           5           GGG        2020-09-08 000020000100001
3           3           CCC        2020-09-08 00003
8           3           HHH        2020-09-08 0000300001
4           4           DDD        2020-09-08 00004
9           4           III        2020-09-08 0000400001
10          10          JJJ        2020-09-08 00005

The key is the sort column, which builds a value that can be sorted alphabetically. Of course, you can exclude the sort column from the final output, but I wanted you to see how it builds the sort data.

The recursive nature of CTE queries means that it will continue to loop and re-run the sub-query until all the children + grandchildren + great-grandchildren, etc., have been pulled. That is why the sort data for the GGG record has 3 parts of data.