August 22, 2003 at 4:06 am
I have an employee table defined like this
IDNameMgr_Id
==============================
1StevenNull
2Mary1
3George6
4Julie2
5Lingo3
6James8
7Eddie2
8Linda1
This table lists the employee and his/her manager.I want to get the output as
Steven
--Mary
---Julie
---Eddie
--Linda
---James
------George
---------Lingo
Note the lines indicate the indundation needed
Any help ?
Edited by - swathi_work on 08/22/2003 04:08:04 AM
August 22, 2003 at 4:14 am
If you have Id field then try using the replicate function to get the required indentation.
select replicate('-',id)+ cast(name as varchar(100))
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
August 22, 2003 at 6:47 am
Hi Swathi,
quote:
I have an employee table defined like thisIDNameMgr_Id
==============================
1StevenNull
2Mary1
3George6
4Julie2
5Lingo3
6James8
7Eddie2
8Linda1
This table lists the employee and his/her manager.I want to get the output as
Steven
--Mary
---Julie
---Eddie
--Linda
---James
------George
---------Lingo
this is the classical example for a SELF JOIN. There are plenty of examples out on the internet. Maybe these one will help you
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnima00/html/ima1100.asp
http://csc.colstate.edu/linton/6127su00/6127.chapter6.selfjoin.htm
http://www.onlamp.com/pub/a/onlamp/2001/07/12/aboutSQL.html
The rest is presentation stuff which should be done at the client
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 23, 2003 at 4:55 am
I am sorry,
Yeah I know about the self join to get the data but I want the result in the order I have specified (even though no indundation)
LIke
Parent
Child
GrandChild
Child
GrandChild
GrandGrandChild
Child
Regards
Swathi
August 23, 2003 at 3:12 pm
Try executing this script. It creates a table and function in tghe tempdb database
USE TEMPDB
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EMPLOYEES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EMPLOYEES]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnGetLevel]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnGetLevel]
GO
CREATE TABLE EMPLOYEES (ID INT, EMP VARCHAR(25), PID INT)
INSERT INTO EMPLOYEES (ID, EMP, PID) VALUES (1, 'STEVEN', NULL)
INSERT INTO EMPLOYEES (ID, EMP, PID) VALUES (2, 'MARY', 1)
INSERT INTO EMPLOYEES (ID, EMP, PID) VALUES (3, 'GEORGE', 6)
INSERT INTO EMPLOYEES (ID, EMP, PID) VALUES (4, 'JULIE', 2)
INSERT INTO EMPLOYEES (ID, EMP, PID) VALUES (5, 'LINGO', 3)
INSERT INTO EMPLOYEES (ID, EMP, PID) VALUES (6, 'JAMES', 8)
INSERT INTO EMPLOYEES (ID, EMP, PID) VALUES (7, 'EDDIE', 2)
INSERT INTO EMPLOYEES (ID, EMP, PID) VALUES (8, 'LINDA', 1)
GO
CREATE FUNCTION fnGetLevel (@MANID INT, @LEVEL INT)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @NEWMANID AS INT
DECLARE @SLEVEL AS VARCHAR(500)
SELECT @NEWMANID = PID FROM EMPLOYEES WHERE ID = @MANID
IF NOT(@NEWMANID IS NULL)
BEGIN
SET @LEVEL = @LEVEL + 1
SET @SLEVEL = dbo.fnGetLevel(@NEWMANID,0) + '-' +
REPLICATE('0', 2 -DATALENGTH(CAST(@LEVEL AS VARCHAR(2)))) + CAST(@LEVEL AS VARCHAR(2)) + '-' +
REPLICATE('0', 5 -DATALENGTH(CAST(@MANID AS VARCHAR(5)))) + CAST(@MANID AS VARCHAR(5))
END
ELSE
BEGIN
SET @SLEVEL = '00' + '-' + REPLICATE('0', 5 -DATALENGTH(CAST(@MANID AS VARCHAR(5)))) + CAST(@MANID AS VARCHAR(5))
END
RETURN @SLEVEL
END
GO
DECLARE @CHILD AS INT
SELECT * FROM
(SELECT ID, EMP, PID, dbo.fnGetLevel(ID, 0) AS LEVEL FROM EMPLOYEES) AS A
ORDER BY LEVEL
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EMPLOYEES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EMPLOYEES]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnGetLevel]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnGetLevel]
Go
I've used recursion to generate a level column which is basically the level and the id of the record at that level. The root is at level 0.
original data
ID EMP PID
1STEVENNULL
2MARY1
3GEORGE6
4JULIE2
5LINGO3
6JAMES8
7EDDIE2
8LINDA1
The results
ID EMP PID LEVEL
1STEVENNULL00-00001
2MARY100-00001-01-00002
4JULIE200-00001-01-00002-01-00004
7EDDIE200-00001-01-00002-01-00007
8LINDA100-00001-01-00008
6JAMES800-00001-01-00008-01-00006
3GEORGE600-00001-01-00008-01-00006-01-00003
5LINGO300-00001-01-00008-01-00006-01-00003-01-00005
For employee Julie the level is
00-00001-01-00002-01-00004
00-00001 is the parent (STEVE)
01-00002 is the child (MARY)
01-00004 is the grandchild (JULIE)
The function assumes that no more than 100 (0-99) levels exist and all id values fall withing the range 0-99999. You can change the values in the replicate lines in the function to adjust.
Navin
Navin Parray
Navin Parray
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply