[Last Call] Learn how to a build a cloud-first strategyRegister Now


Hierarchy Tree Query

Posted on 2006-05-02
Medium Priority
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
  • 4
  • 3
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16590542
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

ID: 16594430
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

ID: 16595301
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
    SELECT e.id, 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 16595715

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

imran_fast earned 750 total points
ID: 16620412
>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

ID: 16736653
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

ID: 16764587
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

ID: 16764592
Are you still there?

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

834 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