March 16, 2007 at 9:16 pm
My Table Structure
Category_ID Number(3)
Parent_ID Number(3) <----Category_ID reports to this colum
Category_Name Varchar(100)
MY QUERY
=============================
WITH Hierarchy(Category_ID, Category_Name, Parent_ID, HLevel)
AS
(
SELECT Category_ID, Category_Name, Parent_ID, 0 as HLevel FROM Dir_Categories
UNION ALL
SELECT SubCategory.Category_ID
, SubCategory.Category_Name,
SubCategory.Parent_ID,
HLevel + 1
FROM Dir_Categories SubCategory
INNER JOIN Hierarchy ParentCategory
ON SubCategory.Parent_ID = ParentCategory.Category_ID )
SELECT Category_ID,
Category_Name = Replicate('__', HLevel) + Category_Name,
Parent_ID,
HLevel
FROM Hierarchy
My OUTPUT============
All the categories under reporting to Parent_ID 0 or continuous, then the ones reporting to 1 and so fourth. Subcategories are not showing within their main categories. I AM GOING NUTS WITH THIS.
WHAT AM I DOING WRONG???
Can you help me please?
March 18, 2007 at 8:27 am
March 18, 2007 at 6:52 pm
Nope, it doesnt. Thanks for heloping out though. This article is the first thing I tried, still returns the same unorganized results.
Thanks
March 19, 2007 at 3:21 am
This is sample code in BOL.
USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;
GO
comparing it with your code,
I do not see a where condition in your union all statement for Anchor member definition.
you may want to put something that defines your anchor element.
March 19, 2007 at 3:58 am
use tempdb
go
drop table Dir_Categories
go
SET NOCOUNT ON
GO
create table Dir_Categories
(
category_id int,
parent_id int,
Category_Name varchar(20)
)
go
insert Dir_Categories select 1,0,'SQL'
insert Dir_Categories select 2,1,'SQL 2000'
insert Dir_Categories select 3,1,'SQL 2005'
insert Dir_Categories select 4,2,'SQL 2000 ENT'
insert Dir_Categories select 5,2,'SQL 2000 STD'
insert Dir_Categories select 6,3,'SQL 2005 EXPRESS'
insert Dir_Categories select 7,3,'SQL 2005 WORKGROUP'
GO
select * from Dir_Categories
go
WITH Hierarchy(Category_ID, Category_Name, Sort, Parent_ID, HLevel)
AS
(
SELECT Category_ID, Category_Name , convert(varchar(255),Category_Name) as Sort, Parent_ID, 0 as HLevel FROM Dir_Categories
WHERE Parent_ID = 0
UNION ALL
SELECT SubCategory.Category_ID , SubCategory.Category_Name as Category_Name, CONVERT (varchar(255), RTRIM(Sort) + '| ' + SubCategory.Category_Name ) as Sort,SubCategory.Parent_ID, HLevel + 1
FROM Dir_Categories SubCategory
INNER JOIN Hierarchy ParentCategory
ON SubCategory.Parent_ID = ParentCategory.Category_ID
)
SELECT
Category_ID,
Category_Name = convert(varchar(20), Replicate('__', HLevel) + Category_Name )
FROM Hierarchy order by sort
go
March 19, 2007 at 8:26 am
Amit, you solved this for me, I really really appreciate it. One more thing, why are some categories kind of cut off in the end.. Check it out
451 ____Speech Recogniti
111 __Artificial Intelli
452 ____Academic Departm
453 ____Agents
454 ____Applications
455 ____Belief Networks
475 ____Cellular Automat
456 ____Conferences and
457 ____Creativity
458 ____Data Mining
459 ____Distributed Proj
March 19, 2007 at 8:39 am
Nevermind, it was the Convert(varchar(20)) it was only allowing 20 chars to display. Dude, THANKS AGAIN!! I have been searching for this answer for a while now. I am not too good at SQL server, oracle is what I deal with mostly and still not an expert.
Jovanky
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply