Hi....
As per the table structure, inorder to get the desired output, I think there is a need to change the table structure and the way records are organized.
Following are the changes:-
1) Keep the parentid as null for the records, which is equal to id i.e. Parentid equal to ID, in this case ID=1 (Expenses) and ID=4 (Incomes). If you have similar records in the future make sure those are null.
2)Since the Level changes or resets from 1 for every group of records, Create a colum for categorizing it or grouping it. For example in your scenario from Expenses to Tiles cost it is leveled from 1-4 and again the level is reset or different for records Incomes and Indirect. Therefore group/categorize it with a column and call it as 'A','B','C' ..... category.
So the table records with above changes looks as follows:-
ID ParentID Level Code Name Category
1 NULL 1 EXP Expenses a
2 1 2 PEXP Project Exp. a
3 1 2 IEXP Indirect Exp. a
4 NULL 1 INC Incomes b
5 1 2 MEXP Misc. Exp. a
6 2 3 MCOST Material Cost a
7 4 2 IINC Indirect Inc. b
8 6 4 TCOS Tiles Cost a
And if you fire the below query with the use of CTE you will get the result as per the category, which will be organized as per the level.
with CTE as
(
Select * from tblhierarchy
where parentid is null and category='a'
union all
Select a.* from tblhierarchy as a inner join cte as b
on a.Parentid=b.id
where a.parentid is not null and a.category='a'
)
Select * from CTE
This will give you the following output.
ID ParentID Level Code Name Category
1 NULL 1 EXP Expenses a
2 1 2 PEXP Project Exp. a
3 1 2 IEXP Indirect Exp. a
5 1 2 MEXP Misc. Exp. a
6 2 3 MCOST Material Cost a
8 6 4 TCOS Tiles Cost a
Now in here the output is equal to yours except the ordering of Code. But with respect to Parentid and level the order is correct. So in order to get the output which you expect you need to make the ID numbering accordingly or order it accordingly.
The other piece of records i.e. different level of records can be seen if you fire the above query by changing the category to 'b'.
Hope it helps and you have understood what i tried to depict.