Ordering within Hierarchy - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Ordering within Hierarchy

Author

Topic

Mickybee
Starting Member

29 Posts

Posted - 2007-08-07 : 13:40:38

Hi all,

Ive got a bit of a problem,

I have two tables:

CATEGORY
id
name
parent_CATEGORY_id

RECORD
id
CATEGORY_id
Stock_Held_Number

ps. Ive stripped out any non relevant fields

I also have the following query (again ive stripped out the non-relevant fields)

SELECT TOP (100) PERCENT SUM(dbo.RECORD.Stock_Held_Number) AS TotalStock, CATEGORY.Name AS FundName
FROM CATEGORY
LEFT OUTER JOIN dbo.RECORD ON CATEGORY.ID = dbo.RECORD.CATEGORY_id
GROUP BY CATEGORY.Name, RECORD.Stock_Held_Number

At the moment its grouping all the CATEGORIES and giving me a sum total for each which is great.

The problem I have is the CATEGORY table, there is an optional join to parent CATEGORY records on the table.

What Im trying to do is to provide a fully ordered result within the CATEGORIES and I don't have a clue.

For example:

The CATEGORY table has the following values
ID Name Parent_Category_id
1 Pens
2 Animals
3 Rocks
4 Horses 2
5 Dogs 2

When I currently run the query I get:
Name TotalStock
Pens 20
Animals 30
Rocks 40
Horses 50
Dogs 60

It's technically correct because I don't want the parent to calculate the total value of the children

What Im really trying to do is order them within the hierarchy though and indent (if possible) the result so I would get

Name TotalStock
Animals 30
---Dogs 60
---Horses 50
Pens 20
Rocks 40

Does anybody have any pointers as to how I can achieve this.

Many thanks

Mike

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-07 : 14:51:40

Indenting is an application isssue, not something you'd do inside T-SQL. So if you want indents, put that code in your front-end application.

Not sure about the ordering thing, I've never worked with hierarchies.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-08 : 02:34:04

As said, you can do this in front end application. But you need to generate a level number to properly indent the result.

Refer this
http://www.nigelrivett.net/SQLTsql/RetrieveTreeHierarchy.html

You can simulate the indents in your front end application the same way described in that link

Madhivanan

Failing to plan is Planning to fail

Mickybee
Starting Member

29 Posts

Posted - 2007-08-08 : 12:29:22

Thanks both,

The indents are a nice to have type thing, what I am not sure about if how to order the results. At the moment they are simply ordered alphabetically but i would like to order them alphabetically within the hierarchy order.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources