Query to group and sort hierarchical data

Question

0.00/5 (No votes)

See more:

I have a table like this in my SQL Server 2008.

ID     ParentID    Level     Code    Name           Description
1      1           1         EXP     Expenses       --
2      1           2         PEXP    Project Exp    --
3      1           2         IEXP    Indirect Exp.  --
4      4           1         INC     Incomes        --
5      1           2         MEXP    Misc. Exp.     --
6      2           3         MCOST   Material Cost  --
7      4           2         IINC    Indirect Inc.  --
8      6           4         TCOS    Tiles Cost    --

I want a query which select all the rows in hierarchical order. (Tiles Cost under Material Cost, Material Cost under Project Expense, Project Expenses under Expenses etc. There can be at most 5 levels. There are 2000 rows in the table.)

Is this possible in SQL query?

The expected result should look like this:

ID     ParentID    Level     Code    Name           Description
1      1           1         EXP     Expenses       --
3      1           2         IEXP    Indirect Exp.  --
5      1           2         MEXP    Misc. Exp.     --
2      1           2         PEXP    Project Exp    --
6      2           3         MCOST   Material Cost  --
8      6           4         TCOS    Tiles Cost    --
4      4           1         INC     Incomes        --
7      4           2         IINC    Indirect Inc.  --

Posted 17-Nov-15 23:29pm

Yesudass Moses

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sums Mohs Eds · Accepted Answer · 2015-11-18T23:15:00

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.

SQL

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.