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.
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.
Please try using self join like this:
SELECT t1.*, t2.Date
FROM TableName t1
INNER JOIN TableName t2 ON t2.ID = t1.ParentId
You're likely looking to sort all of the data hierarchically. This can be best done with CTE's
In your case the SQL would look like this:
with t as
(select 1 id, 'aaa' name, cast('20200907' as date) date, 1 parentid union
select 2 id, 'bbb' name, cast('20200908' as date) date, 2 parentid union
select 3 id, 'ccc' name, cast('20200909' as date) date, 3 parentid union
select 4 id, 'ddd' name, cast('20200910' as date) date, 4 parentid union
select 5 id, 'eee' name, cast('20200911' as date) date, 2 parentid union
select 6 id, 'fff' name, cast('20200912' as date) date, 1 parentid union
select 7 id, 'ggg' name, cast('20200913' as date) date, 5 parentid union
select 8 id, 'hhh' name, cast('20200914' as date) date, 3 parentid union
select 9 id, 'iii' name, cast('20200915' as date) date, 4 parentid union
select 10 id, 'jjj' name, cast('20200916' as date) date, 10 parentid)
, t1 (id, name, date, parentid, level) as (
select id, name, date, parentid, 0 from t where id = parentid --starting condition (top level, no higher ancestors)
union all
select t.id, t.name, t.date, t.parentid, t1.level + 1 --level goes down
from t join t1 on t.parentid = t1.id --hierarchy condition, add those elements that have a direct ancestor already selected
and t.id != t1.parentid --cannot reselect them, otherwise the recursion won't stop
)
select * from t1 order by level, id
The end result looks like this
On a simple dataset that is a hierarchy with a finite number of levels that is small, you can do this by simply self joining.
You could use this method instead of a
CTEif your database engine does not supportCTEs or if you needed to keep the query maintenance simple.
Setup using table variable
DECLARE @table table (
id int,
name varchar(10),
[date] date,
parentid int
)
insert into @table 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)
Query from the @table with 3 levels of depth
SELECT child.id, child.name, child.[date], child.parentId
FROM @table child
LEFT OUTER JOIN @table parent on child.parentid = parent.id
ORDER BY parent.parentid, child.parentid, child.id
If your data structure has nulls to indicate that there is no parent, then this query can be more efficient again, but we have to COALESCE the Ids:
SELECT child.id, child.name, child.[date], child.parentId
FROM @table child
LEFT OUTER JOIN @table parent on child.parentid = parent.id
order by COALESCE(parent.parentid,child.parentid,child.Id), IsNull(child.parentid,child.Id), child.id
Finally, to support each additional level of recursion, add another join, ultimately you will need n-2 joins where n is the maximum number of levels your data will support, the following supports 4 levels:
SELECT child.id, child.name, child.[date], child.parentId
FROM @table child
LEFT OUTER JOIN @table parent on child.parentid = parent.id
LEFT OUTER JOIN @table grandparent on parent.parentid = grandparent.id
order by COALESCE(grandparent.parentId, parent.parentid,child.parentid,child.Id), COALESCE(parent.parentid,child.parentid,child.Id), IsNull(child.parentid,child.Id), child.id
