[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

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


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

  • 3
  • 2
1 Solution
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
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.
Sorry I was looking at the wrong picture. The second one os definitely correct.
In which case your problem is probably using COALESCE. Since neither categoryid and catparentid are NULL, coalesce always returns the first argument.
Put your coalesce expressions in the select list and see what I mean,
...... or use the heirarchyid data type which is built for this kind of thing.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Mark WillsTopic AdvisorCommented:
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,
	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

doctor069Author Commented:
sorry for the delay
works great
- thanks
Mark WillsTopic AdvisorCommented:
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...

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now