asked on

SQL Hierarchical Data

Hi -

Below is my sql 2008 query to attempt to output hierarchical data. My table is in the attached image "sql-table" and the result set is attached as "sql-result"

The REPLICATE works find but the order of the categories is not

For example the categoryid:133 has a catparentid of 131 so should fall directly under the 131 row. Also if I add a "child" to a "child"... two levels; the order get even worse.

I am not sure if my query is the best way to get hierarchical data (treeview type data)

Open to suggestions...

Thanks

Select [categoryid], REPLICATE('...', level)  + catname AS catname, [catparentid]
From [dbo].[POM.Categories]
Order By Coalesce([categoryid],[catparentid]), Coalesce([catparentid], 0), [catname]

Open in new window

sql-table.gif
sql-result.gif

nmcdermaid

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.

nmcdermaid

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.

ASKER CERTIFIED SOLUTION

nmcdermaid

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

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.

Open in new window

doctor069

ASKER

sorry for the delay
works great
- thanks

Mark Wills

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...