Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SQL Query Parent Child Treeview

Posted on 2010-01-08
Medium Priority
Last Modified: 2012-05-08

I am using SQL 2008.

I have a table that has parent child relation ships in it (see below). I use the following queery to display the rows in the associated order (I think it works).

Select [categoryid],catname, [catparentid]
From [dbo].[POM.Categories]
Order By Coalesce([catparentid] , [categoryid]),  
   Coalesce([catparentid], 0), [catname]

I would like to add  a "..." in front of the catname based on the level "..." - level 1 "......"-level2 etc. So that It will create a treeview.


categoryid      catparentid      level      catname
38                       NULL           0           Category1
39                         38             1        ChildCat1A
40                         38             1        ChildCat1B
41                         38             1        ChildCat1C
42                         38             1        ChildCat1D
43                         42             2        ChildCat1D_A
44                         42             2        ChildCat1D_B
45                        NULL           0      Category2
46                          45             1        ChildCat2A
47                          45             1        ChildCat2B
48                          45             1        ChildCat2C
Question by:doctor069
LVL 41

Accepted Solution

ralmada earned 2000 total points
ID: 26212370

Do you have a level column?

select categoryid, catparentid, level, coalesce(replicate('...', level), '') + catname from yourtable

Author Closing Comment

ID: 31674689
Perfect!! Thanks
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26212438
You can use replicate for that:
Select [categoryid], REPLICATE('...', level) + catname, [catparentid]
From [dbo].[POM.Categories]
Order By Coalesce([catparentid] , [categoryid]),  
   Coalesce([catparentid], 0), [catname]

but you have a logic flaw if you ever have data not in perfect caegoryid order, you need to use a CTE to properly build your tree.

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question