SQL Query Parent Child Treeview

Posted on 2010-01-08
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


    Do you have a level column?

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

    Author Closing Comment

    Perfect!! Thanks
    LVL 26

    Expert Comment

    by:Chris Luttrell
    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

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    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…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    This video discusses moving either the default database or any database to a new volume.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now