displaying tree structure .. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

displaying tree structure ..

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2006-08-31 : 08:04:03

Hi Team,

One small doubt. I need to display tree structure format using T-SQL.
I have done it in oracle. for this i 've taken 2 tables DEPT (parent) and EMP (child).

CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

Now by issuing the follwoing qry u can get the tree structure as follows.

SQL> select rpad( '*', (level-1)*2, '*' ) || ename "ename", empno, mgr
from emp start with nvl(mgr,-1) = -1
connect by prior empno = mgr

ename EMPNO MGR
-------------------- ---------- ----------
KING 7839
**JONES 7566 7839
****SCOTT 7788 7566
******ADAMS 7876 7788
****FORD 7902 7566
******SMITH 7369 7902
**BLAKE 7698 7839
****ALLEN 7499 7698
****WARD 7521 7698
****MARTIN 7654 7698
****TURNER 7844 7698
****JAMES 7900 7698
**CLARK 7782 7839
****MILLER 7934 7782

14 rows selected.

Now my doubt is how can i do the same in SQL SERVER 2000 .
If anyone knows plz help me

Best Regards,
franky

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-31 : 08:23:47

Definitely not as easy in SQL 2000 as it is in SQL 2005....try this

SELECT seq=IDENTITY(int),
level=CASE WHEN o2.empno=o2.mgr THEN 0 ELSE 1 END,
empno=o2.empno,
mgr=o1.empno
INTO #Hierarchy
FROM Emp o1 INNER JOIN Emp o2 ON (o1.empno=o2.mgr)

WHILE (@@rowcount > 0) 
BEGIN
	INSERT #Hierarchy (level, empno, mgr)
	SELECT DISTINCT o1.level+1, o2.empno, o1.mgr
	FROM #Hierarchy o1 INNER JOIN #Hierarchy o2 ON (o1.empno=o2.mgr)
	WHERE o1.level=(SELECT MAX(level) FROM #Hierarchy)
	AND o1.empno<>o1.mgr
END

SELECT OrgChart=REPLICATE('*',level)+s.ename
FROM (SELECT
empno,
seq=MIN(seq),
level=MAX(level)
FROM #Hierarchy
GROUP BY empno) o INNER JOIN emp s ON (o.empno=s.empno)
ORDER BY o.seq

I haven't checked it, just check it out!

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-31 : 12:42:15

Also search on Trees and Hierarchies in the article section

Madhivanan

Failing to plan is Planning to fail

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2006-09-01 : 00:38:41

It almost solved my problem

Thanks a lot but KING is not getting displayed.

I got the output something like this

***SMITH
**ALLEN
**WARD
*JONES
**MARTIN
*BLAKE
*CLARK
**SCOTT
**TURNER
***ADAMS
**JAMES
**FORD
**MILLER

quote:

Originally posted by harsh_athalye

Definitely not as easy in SQL 2000 as it is in SQL 2005....try this

SELECT seq=IDENTITY(int),
level=CASE WHEN o2.empno=o2.mgr THEN 0 ELSE 1 END,
empno=o2.empno,
mgr=o1.empno
INTO #Hierarchy
FROM Emp o1 INNER JOIN Emp o2 ON (o1.empno=o2.mgr)

WHILE (@@rowcount > 0) 
BEGIN
	INSERT #Hierarchy (level, empno, mgr)
	SELECT DISTINCT o1.level+1, o2.empno, o1.mgr
	FROM #Hierarchy o1 INNER JOIN #Hierarchy o2 ON (o1.empno=o2.mgr)
	WHERE o1.level=(SELECT MAX(level) FROM #Hierarchy)
	AND o1.empno<>o1.mgr
END

SELECT OrgChart=REPLICATE('*',level)+s.ename
FROM (SELECT
empno,
seq=MIN(seq),
level=MAX(level)
FROM #Hierarchy
GROUP BY empno) o INNER JOIN emp s ON (o.empno=s.empno)
ORDER BY o.seq

I haven't checked it, just check it out!

Harsh Athalye
India.
"Nothing is Impossible"

bernardofm
Starting Member

1 Post

Posted - 2006-09-06 : 16:58:58

Ok. Help me please...

A table Group:
GRP_ID
GRP_ID_FATHER

How to Select in this structure in SQL 2000 ?

Tanks...

Bernardo.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources