hierarchy Path - 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 2008 Forums

Transact-SQL (2008)

hierarchy Path

Author

Topic

IK1972

56 Posts

Posted - 2012-09-10 : 20:05:54

I have one table with 4 fileds

EmpId, ParentID, Name, Title

we have hierarchy data in this table. I want to get a Path for every user.

Like
ID - Name - Path
1 - a - /
2 - b - /a
3 - c - /b/a

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 20:18:02

[code]
;With Emp_Hierarchy
AS
(
SELECT EmpId,ParentID,Name, CAST('/' AS varchar(max)) AS [Path]
FROM table
WHERE ParentID IS NULL

UNION ALL

SELECT t.EmpId,
t.ParentID,
t.Name,
e.[Path] + e.[Name]
FROM Emp_Hierarchy e
INNER JOIN table t
ON t.ParentID = e.EmpId
)

SELECT EmpID AS ID,
[Name],
[Path]
FROM Emp_Hierarchy
[/code]

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

IK1972

56 Posts

Posted - 2012-09-11 : 16:24:48

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 16:26:00

welcome

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

IK1972

56 Posts

Posted - 2012-09-11 : 23:27:52

I try this and I saw this is from Top to Bottom. Do you how I can get a path from Bottom to Top.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 23:57:56

[code]
;With Emp_Hierarchy
AS
(
SELECT EmpId,ParentID,Name, CAST('/' AS varchar(max)) AS [Path]
FROM table t1
LEFT JOIN table t2
ON t2.ParentID = t1.EmpId
WHERE t2.EmpID IS NULL

UNION ALL

SELECT t.EmpId,
t.ParentID,
t.Name,
e.[Path] + e.[Name]
FROM Emp_Hierarchy e
INNER JOIN table t
ON t.EmpID= e.ParentID
)

SELECT EmpID AS ID,
[Name],
[Path]
FROM Emp_Hierarchy
[/code]

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

IK1972

56 Posts

Posted - 2012-09-12 : 14:09:02

No its not giveing expected result from Boton to Top. I saw you mentioend EMPID is NULL in first part. I dont think so I have mull in EMPID.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 14:17:13

see LEFT JOIN usage. that logic was to identify the child records (bottom most).

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 14:43:26

show us what your intended result is out of a sample data.

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

IK1972

56 Posts

Posted - 2012-09-12 : 15:28:47

create table #emp(ID int, ParentID int, Name varchar(20))
insert into #emp values
(0, null, 'a'),
(1,0,'b'),
(2,0,'c'),
(3,1,'d'),
(4,3,'e'),
(5,4,'f')

select * from #emp

First expected result as you can see its start from user and end till root.
-- expect result
ID, Name, Path
1, b, a
2, c, a
3, d, d/b/a
4, e, e/d/b/a
5 f, f/e/d/b/a

second expected result is its start from user and end before root level.

-- expect result
ID, Name, Path
1, b,
2, c,
3, d, d/b/
4, e, e/d/b/
5 f, f/e/d/b/

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 15:45:05

your path value is not consistent

why does record for b doesnt have itself included in path (value is just a) whereas in next level you've value for d as d/b/a

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

IK1972

56 Posts

Posted - 2012-09-12 : 16:40:02

Yes this is type mistake. even for b if I get b/a its fine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 17:16:42

[code]
;With Emp_Hierarchy
AS
(
SELECT t1.ID,t1.ParentID,t1.Name, CAST(t1.Name AS varchar(max)) AS [Path]
FROM #emp t1
WHERE t1.ParentID IS NULL

UNION ALL

SELECT t.ID,
t.ParentID,
t.Name,
CAST(t.[name] + '/' + e.[path] AS varchar(max))
FROM Emp_Hierarchy e
INNER JOIN #emp t
ON t.ParentID= e.ID
)

SELECT ID,
[Name],
[Path]
FROM Emp_Hierarchy

output
---------------------
0 a a
1 b b/a
2 c c/a
3 d d/b/a
4 e e/d/b/a
5 f f/e/d/b/a

[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources