SQL Hierarchical Data

Do you know about the new heirarchyid data type in SQL Server 2008? Maybe this would make things easier for you.
Alternatively you could order by this
SELECT * FROM YourTable
ORDER BY (CASE WHEN catparentid = 0 THEN categoryid ELSE catparentid END), categoryid
This will place children under their parent.

I see you are using COALESCE in your SQL (implying NULLS) but your data is showing zeros. Does your data contain zeros or NULLs? That picture of data is definitely not what is coming out of your query.

Being SQL 2008 it does seem a bit of a pity not to use the heirarchyid - because as nmcdermaid says it is purpose built for this typ of thing. However, it would also meen a pretty big change to your routines to add and maintain let alone generalised queries.

For this type of thin I normaly revert to a recursive CTE query

Here we use the actual ID's to get a "tree_path" - we use the ID so it is numerically sortable... and for the purposes of this test have assumed a max of 6 digit numbers...

Please check the sample below - also note how we do not have to have all the columns, just the categoryid and the CatParentID to get our tree_path.

-- step 1 - lets create some sample data

--if object_id('tempdb..#pom_categories','U') is not null drop table #pom_categories

create table #POM_Categories (categoryID int, CatParentID int,CatName varchar(100))
 
insert into #POM_Categories values (131,0,'Tester')
insert into #POM_Categories values (132,0,'test')
insert into #POM_Categories values (133,131,'tesew')
insert into #POM_Categories values (134,132,'more test')
insert into #POM_Categories values (136,134,'kid of more test')
insert into #POM_Categories values (138,133,'kid of tesew')
insert into #POM_Categories values (139,138,'kid of kid of tesew')


-- OK, now the query itself

;WITH PCHierarchy AS
(--Base Query
	SELECT  1 AS Level,p.catname,
            convert(varchar(8000),right('000000'+convert(varchar,CatParentID),6)) as Tree_Path,
            convert(varchar(8000),right('000000'+convert(varchar,CatParentID),6)) as Indented_Tree,
            CategoryID as ID
    From #POM_Categories P
    where not exists (select NULL from #POM_Categories C where P.CatParentID = C.CategoryID)

    UNION ALL  -- recursive bit

	SELECT  Level + 1,p.CatName,
            tree_path + right('000000'+convert(varchar,P.CategoryID),6),  -- zero fills a 6 digit code
            replicate('   ',Level+1) + convert(varchar(8000),right('000000'+convert(varchar,P.CategoryID),6)) as Indented_Tree,
            P.CategoryID
	FROM    PCHierarchy H 
	JOIN    #POM_Categories P ON P.CatParentID = H.ID

) --Final Select
SELECT H.Level, H.ID, H.catname, H.Tree_path, H.Indented_Tree
FROM PCHierarchy H
ORDER BY H.Tree_path

-- more like a tree - makes path_tree_name more realistically indented
-- check it out if you order by level and employeeno - doesn't show relationship as a tree.

Select all Open in new window

sorry for the delay
works great
- thanks

Wow, interesting solution. Would love to see your final select without the coalesce. You do have all the attributes in the tables, so long as the relationships are uniquely defined and "ordered" the right way. Where as, using the child/parent relationship to traverse the data overcomes some of those problems.

Anyway, glad to see you have your solution...