Hierarchy Tree Query

Posted on 2006-05-02
Last Modified: 2008-01-09
Listed below is a query that displays a hierarchy tree in Oracle. What is the syntax to do this same query in MSSQL(T-SQL)?

Oracle Hierarchy Query:
select id, sys_connect_by_path(description,' > ')tree,cat_level
from ticket_category_tmp
start with parent_cat_code=0
connect by prior id=parent_cat_code
Order By tree

Results From Query:
ID     TREE                                                                   CAT LEVEL
7      > Administration                                                        0
115      > Administration > Authorizations                            1
116      > Administration > Authorizations > Changeout         2
117      > Administration > Authorizations > Trip                   2
114      > Administration > Password Change                       1
113      > Administration > UserID Change                            1
Question by:Omega002
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    what version of SQL Server?

    SQL Server 2005: you can use the CTE expression to make the same.
    SQL Server 2000: you have to build this using a stored function/procedure
    LVL 28

    Expert Comment

    for sql server 2000:
    create function hirearchy(@id as int) returns varchar(1000)
    declare @tree as varchar(1000)
    set @tree = ''
    if (parent_cat_code <> 0 and  parent_cat_code is not  null )

    select @tree = dbo.hirearchy(parent_Cat_code) +'>' + description from ticket_category_tmp where id = @id

    return @tree

    select id, dbo.hirearchy(id) as tree,cat_level
    from ticket_category_tmp
    Order By dbo.hirearchy(id)

    LVL 28

    Expert Comment

    for sql 2005
    WITH DirectReports(id , description, cat_level) AS
        SELECT id, description, 0 AS cat_level
        FROM ticket_category_tmp
        WHERE parent_cat_code =0
        UNION ALL
        SELECT, e.description, cat_level + 1
        FROM ticket_category_tmp e
            INNER JOIN DirectReports d
            ON e.parent_cat_code = d.EmployeeID
    SELECT id , description, cat_level
    FROM DirectReports


    Author Comment


    I got the following error when I attempted your sql 2000 remedy:
    Server: Msg 207, Level 16, State 3, Procedure hirearchy, Line 6
    Invalid column name 'parent_cat_code'.
    Server: Msg 207, Level 16, State 1, Procedure hirearchy, Line 6
    Invalid column name 'parent_cat_code'.
    Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name 'dbo.hirearchy'.
    Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name 'dbo.hirearchy'.
    LVL 28

    Accepted Solution

    >Invalid column name 'parent_cat_code'.

    what is the structure of your table ticket_category_tmp does this column exists there please post the schema for this table.

    Author Comment

    Got a questions for you. How would I get a count of all the tickets from this hieracrchy query? here is the query listed below but how could I get the number of tickets in a date range for each category?

    SELECT     dbo.ticket_category_tmp.ID, dbo.hirearchy(dbo.ticket_category_tmp.ID) AS tree, dbo.ticket_category_tmp.SITE_ID, dbo.ticket.entered,
                          dbo.ticket.pri_code, dbo.ticket.problem, dbo.ticket.ticketid
    FROM         dbo.ticket_category_tmp INNER JOIN
                          dbo.ticket ON dbo.ticket_category_tmp.ID = dbo.ticket.ticketid
    WHERE     (dbo.ticket.entered BETWEEN '20060101' AND '20060531')
    ORDER BY dbo.hirearchy(dbo.ticket_category_tmp.ID)

    Author Comment

    CREATE TABLE [ticket_category] (
          [id] [int] NOT NULL ,
          [cat_level] [int] NOT NULL ,
          [parent_cat_code] [int] NOT NULL ,
          [description] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
          [full_description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
          [default_item] [bit] NULL ,
          [level] [int] NULL ,
          [enabled] [bit] NULL ,
          [site_id] [int] NULL

    Author Comment

    Are you still there?

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    734 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

    19 Experts available now in Live!

    Get 1:1 Help Now