• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2046
  • Last Modified:

create hierarchy using T-SQL!

I have a table which shows categoryID and its parentID like this:
Year   Month    catID       catName                   parentID    
2007      5          1001       PDA & Handhelds     1000
2007      5          1002       PDAs                         1001
2007     5           1003       notebooks                 1000
2007     5            1004      desktops                    1000    
2007     5            1000      computers                    0    


I need to create a hierarchy table based on info from the above table. I don't want to use self-join many times as I don't know how many layers there would be. I know an example from the book 'guru's guide to T-SQL' which can get close to what i want to get. The only difference is that I want to put different hierarchy in different column instead of indenting it. The code to do the hierarchy is somewhat like this:

INSERT staff VALUES (1,'GROUCHO',1)
INSERT staff VALUES (2,'CHICO',1)
INSERT staff VALUES (3,'HARPO',2)
INSERT staff VALUES (4,'ZEPPO',2)
INSERT staff VALUES (5,'MOE',1)
INSERT staff VALUES (6,'LARRY',5)
INSERT staff VALUES (7,'CURLY',5)
INSERT staff VALUES (8,'SHEMP',5)
INSERT staff VALUES (9,'JOE',8)
INSERT staff VALUES (10,'CURLY JOE',9)

SELECT seq=IDENTITY(int),
chartdepth=CASE WHEN o2.employee=o2.supervisor THEN 0 ELSE 1 END,
employee=o2.employee,
supervisor=o1.employee
INTO #org_chart
FROM staff o1 INNER JOIN staff o2 ON (o1.employee=o2.supervisor)
WHILE (@@rowcount > 0) BEGIN
INSERT #org_chart (chartdepth, employee, supervisor)
SELECT DISTINCT o1.chartdepth+1, o2.employee, o1.supervisor
FROM #org_chart o1 INNER JOIN #org_chart o2 ON (o1.employee=o2.supervisor)
WHERE o1.chartdepth=(SELECT MAX(chartdepth) FROM #org_chart)
AND o1.employee<>o1.supervisor
END
SELECT OrgChart=REPLICATE(CHAR(9),chartdepth)+s.employee_name
FROM (SELECT
employee,
seq=MIN(seq),
chartdepth=MAX(chartdepth)
FROM #org_chart
GROUP BY employee) o INNER JOIN staff s ON (o.employee=s.employee)
ORDER BY o.seq

If you run the above you would understand what I'm trying to get but i want the result look like this:        
level1                 level2              level3              level4              level5
GROUCHO         CHICO             HARPO
GROUCHO         CHICO             ZEPPO
GROUCHO         MOE                LARRY
GROUCHO         MOE               CURLY
GROUCHO         MOE               SHEMP             JOE                CURLY JOE

Thanks a lot for the help!
0
qinyan
Asked:
qinyan
  • 2
  • 2
1 Solution
 
qinyanAuthor Commented:
sorry you need this part too:

CREATE TABLE staff (employee int PRIMARY KEY, employee_name varchar(10),
supervisor int NULL REFERENCES staff (employee))
0
 
Faiga DiegelSr Database EngineerCommented:
What version is your SQL? 2000 or 2005?
0
 
qinyanAuthor Commented:
2005
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now