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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.