Department Names in a hierarchical fashion???? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Department Names in a hierarchical fashion????

Author

Topic

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2009-02-12 : 23:45:22

DepartmentID | DepartmentName | ParentDepartmentID
42 | Loss Mitigation | 16
25 | Information Technology | 18
16 | Servicing | 32
18 | Shared Services | 32
32 | Bank Of America | NULL
56 | Foreclosure | 16
59 | Bankruptcy | 16

From the above table I need to show the "List of the DepartmentNames in a hierarchical fashion. Start with the Departments that do not have a parent Department"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-13 : 00:23:56

try like this
;with cte(id,deptname,parentid,parentdept)
as
(
select DepartmentID,DepartmentName,ParentDepartmentID,DepartmentName from urtable
union all
select t.DepartmentID,t.DepartmentName,t.ParentDepartmentID,c.deptname
from urtable t
inner join cte c on c.id = t.ParentDepartmentID
)
select distinct * from cte where deptname <> parentdept

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-13 : 00:30:49

Hi ,

Is this is what you want

declare @temp table ( DepartmentID int, DepartmentName varchar(256), ParentDepartmentID int )
insert into @temp
select 42 , 'Loss Mitigation' , 16 union all
select 25 , 'Information Technology' , 18 union all
select 16 , 'Servicing' , 32 union all
select 18 , 'Shared Services' , 32 union all
select 32 , 'Bank Of America' , NULL union all
select 56 , 'Foreclosure' , 16 union all
select 59 , 'Bankruptcy' , 16

SELECT * FROM @TEMP
ORDER BY CASE WHEN ParentDepartmentID IS NULL THEN 0 ELSE 1 END

If not Please send the expected output...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 01:25:05

[code]declare @test table
(DepartmentID int,
DepartmentName varchar(50),
ParentDepartmentID int)
insert into @test
select 42,'Loss Mitigation',16 union all
select 25 ,'Information Technology', 18 union all
select 16 ,'Servicing', 32 union all
select 18 ,'Shared Services', 32 union all
select 32 ,'Bank Of America', NULL union all
select 56 ,'Foreclosure', 16 union all
select 59 ,'Bankruptcy', 16

;with cte(id,deptname,parentid,parentdept,path,level)
as
(
select DepartmentID,DepartmentName,ParentDepartmentID,cast(NULL as varchar(50)),CAST(DepartmentID AS varchar(max)),0
from @test
where ParentDepartmentID is null
union all
select t.DepartmentID,t.DepartmentName,t.ParentDepartmentID,c.deptname,CAST(t.DepartmentID AS varchar(10))+','+c.path,level+1
from @test t
inner join cte c on c.id = t.ParentDepartmentID
)
select * from cte order by level

output
-----------------------------------------------------
id deptname parentid parentdept path level
32 Bank Of America NULL NULL 32 0
16 Servicing 32 Bank Of America 16,32 1
18 Shared Services 32 Bank Of America 18,32 1
25 Information Technology 18 Shared Services 25,18,32 2
42 Loss Mitigation 16 Servicing 42,16,32 2
56 Foreclosure 16 Servicing 56,16,32 2
59 Bankruptcy 16 Servicing 59,16,32 2[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources