Heirarchical query display - 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

General SQL Server Forums

New to SQL Server Programming

Heirarchical query display

Author

Topic

learntsql

524 Posts

Posted - 2010-08-17 : 09:39:36

Hi All,
I have to display Manager and his subordinates in heirarchical manner
following is the sample data.

CREATE TABLE sampletbl(EmpNo INT PRIMARY KEY,ENAME VARCHAR(50),MGR INT NULL)

INSERT INTO sampletbl VALUES(101,'Kishore',NULL)

INSERT INTO sampletbl VALUES(102,'HARI',101)

INSERT INTO sampletbl VALUES(103,'RAJEEV',101)

INSERT INTO sampletbl VALUES(104,'MANOJ',102)

INSERT INTO sampletbl VALUES(105,'KRISHNA',102)

INSERT INTO sampletbl VALUES(106,'DAVID',102)

INSERT INTO sampletbl VALUES(107,'Anwar',102)

INSERT INTO sampletbl VALUES(108,'Chandu',103)

INSERT INTO sampletbl VALUES(109,'Srikanth',103)

INSERT INTO sampletbl VALUES(110,'Ravi',103)

INSERT INTO sampletbl VALUES(111,'Ashok',103)

INSERT INTO sampletbl VALUES(112,'Nagesh',102)

INSERT INTO sampletbl VALUES(113,'Teja',110)

INSERT INTO sampletbl VALUES(114,'Ramesh',110)

INSERT INTO sampletbl VALUES(115,'Nitin',110)

INSERT INTO sampletbl VALUES(116,'Peter',110)

INSERT INTO sampletbl VALUES(117,'Imran',110)

INSERT INTO sampletbl VALUES(118,'Singh',117)

INSERT INTO sampletbl VALUES(119,'Yashwanth',117)

select * from sampletbl

If 101 logged In

- HARI
- - MANOJ
- - KRISHNA
- - DAVID
- - Anwar
- - Nagesh
- RAJEEV
- - Chandu
- - Srikanth
- - Ravi
- - - Teja
- - - Ramesh
- - - Nitin
- - - Nitin
- - - Peter
- - - Imran
- - - - Singh
- - - - Yashwanth
- - Ashok

IF 102 Logged In

- MANOJ
- KRISHNA
- DAVID
- Anwar
- Nagesh

IF 103 Logged In

- Chandu
- Srikanth
- Ravi
- - Teja
- - Ramesh
- - Nitin
- - Nitin
- - Peter
- - Imran
- - - Singh
- - - Yashwanth
- Ashok

I want data along with EmpId. I want this data in a table format as I want to display this names in the Application Dropdownlist control.
TIA.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-17 : 10:12:30

see

http://msdn.microsoft.com/en-us/library/ms186243.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-17 : 12:26:46

this is what you are looking for:


declare @mgr smallint; 
set @mgr = 101
print @mgr

select * from sampletbl

;with sample_cte(empno, ename, mgr, lvl)
as
 (
	select empno,ename,mgr, 0 as lvl
			from sampletbl where mgr is null
    union all
	select s2.empno,s2.ename,s2.mgr, lvl + 1
		from sampletbl  as s2
		join sample_cte  as c
			on s2.mgr = c.empno

 )

select 
--*
 isnull(c0.lvl,'') as lvl0
,isnull(c0.ename,'') as name0
,isnull(c1.lvl,'') as lvl1
,isnull(c1.ename,'') as name1
,isnull(c2.lvl,'') as lvl2
,isnull(c2.ename,'') as name2
,isnull(c3.lvl,'') as lvl3
,isnull(c3.ename,'') as name3
,isnull(c4.lvl,'') as lvl4
,isnull(c4.ename,'') as name4
	from sample_cte as c0
	left join sample_cte as c1
	on c0.empno = c1.mgr
	left join sample_cte as c2
	on c1.empno = c2.mgr
	left join sample_cte as c3
	on c2.empno = c3.mgr
	left join sample_cte as c4
	on c3.empno = c4.mgr
where @mgr = c1.mgr
order by c1.lvl

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-17 : 13:03:10

what if level goes beyond 4? the solution is not definitely scalable. Better to use recursive solution given in link

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-17 : 13:15:42

true. it is not 100% scalable :)

i haven't checked the link. i will :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-17 : 13:17:42

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-08-18 : 17:17:55

Google "Nested Set Model" or get a copy of TREES & HIERARCHIES IN SQL. There are much better ways to do this in SQL with sets instead of procedural code.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources