CREATE TABLE dept
(
    deptId int,
    deptPid int,
    deptName varchar(20)
)
INSERT INTO dept 
SELECT 1, 0, '财务部' UNION 
SELECT 2, 0, '市场部' UNION 
SELECT 3, 0, 'IT部' UNION 
SELECT 4, 3, '开发部' UNION 
SELECT 5, 3, '测试部' UNION 
SELECT 6, 4, '美工' 

SELECT * FROM dept
/**
要求结果如下:
deptId       deptPid         deptName         
1            0               财务部          
2            0               市场部          
5            3               IT部-测试部      
6            4               IT部-开发部-美工 
**/

CREATE TABLE dept
(
    deptId int,
    deptPid int,
    deptName varchar(20)
)
INSERT INTO dept 
SELECT 1, 0, '财务部' UNION 
SELECT 2, 0, '市场部' UNION 
SELECT 3, 0, 'IT部' UNION 
SELECT 4, 3, '开发部' UNION 
SELECT 5, 3, '测试部' UNION 
SELECT 6, 4, '美工' 

if object_id('dbo.ff',N'FN') is not null drop function dbo.ff
go
create function ff(@id int) returns varchar(100)
as
begin
  declare @str as varchar(100)
  set @str = ''
  select @str = deptName from dept where deptId = @id
  while exists (select 1 from dept where deptId = @id and deptPid<>0)
    begin
      select @id = b.deptId , @str = b.deptName + '-' +@str  from dept a , dept b where a.deptId = @id and a.deptPid = b.deptId
    end
  return @str
end
go


select deptId,deptPid,deptName=dbo.ff(deptId) from dept d
where  not exists(select 1 from dept where deptPid=d.deptId)
/*
deptId      deptPid     deptName
----------- ----------- ----------------------------------------------------------------
1           0           财务部
2           0           市场部
5           3           IT部-测试部
6           4           IT部-开发部-美工

(4 行受影响)


*/

------解决方案--------------------

SQL code

CREATE TABLE dept
(
    deptId int,
    deptPid int,
    deptName varchar(max)
)
INSERT INTO dept 
SELECT 1, 0, '财务部' UNION 
SELECT 2, 0, '市场部' UNION 
SELECT 3, 0, 'IT部' UNION 
SELECT 4, 3, '开发部' UNION 
SELECT 5, 3, '测试部' UNION 
SELECT 6, 4, '美工' 
GO
/**
要求结果如下:
deptId       deptPid         deptName         
1            0               财务部          
2            0               市场部          
5            3               IT部-测试部      
6            4               IT部-开发部-美工 
**/

;with ach as
(
    select deptid,deptpid,deptname,0 as rid from dept
    union all
    select a.deptid,a.deptpid,b.deptname+'-'+a.deptname,b.rid+1 as rid
    from dept a join ach b on a.deptpid = b.deptid
)

select deptid,deptpid,deptname
from ach t
where not exists (select 1 from ach where deptid = t.deptid and rid > t.rid)
    and not exists (select 1 from ach where deptpid = t.deptid)

drop table dept

/**************

deptid      deptpid     deptname
----------- ----------- ----------------------------------------------------------------------------------------------------------------
1           0           财务部   
2           0           市场部   
5           3           IT部-测试部    
6           4           IT部-开发部-美工      

(4 行受影响)

------解决方案--------------------

SQL code
;with t as
(
    select deptId , deptPid = deptId,deptName from dept 
    union all
    select t.deptId , deptPid = d.deptPid,d.deptName from t inner join dept d on t.deptPid = d.deptId
) 
select deptId , deptPid,[path]=reverse(substring(reverse([path]) , charindex(',' , reverse([path])) + 1 , len([path])))   from
(
select deptId , deptPid,
       [path] = STUFF((SELECT '-' + deptName FROM t WHERE deptId = d.deptId order by t.deptId , t.deptPid FOR XML PATH('')) , 1 , 1 , '') 
from dept d
group by deptId , deptPid
) a where not exists(select 1 from dept where deptPid=a.deptId)
/*
deptId      deptPid     path
----------- ----------- --------------------------------------------------------