Hierarchy Tree Query

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
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
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)

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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Omega002Author Commented:

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'.
>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.

Experts Exchange Solution brought to you by ConnectWise

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
Omega002Author Commented:
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)
Omega002Author Commented:
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
Omega002Author Commented:
Are you still there?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.