asked on

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

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

imran_fast

for sql server 2000:
create function hirearchy(@id as int) returns varchar(1000)
as
begin
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
end
go

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

imran_fast

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

GO

Omega002

ASKER

Greetings,

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

ASKER CERTIFIED SOLUTION

imran_fast

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Omega002

ASKER

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)

Omega002

ASKER

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
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Omega002

ASKER

Are you still there?