生成管理结构表

Question

大家好！我们公司的管理结构有五层，从上到依次为:
第一层： Area Manager      ID以AM开头
第二层： Project Manager   ID以PM开头
第三层： Superivor         ID以SUP开头
第四层： Site Manager      ID以SM开头
第五层： Staff    ID以CC开头每一个员工只有一个上级， 但是这个上级可能是跨层，比如Staff的上级可以是Area Manager。在数据库中存储表如下：员工编号  领导编号
CC8       AM1
CC1       PM1
CC2       PM1
CC3       SUP1
CC4       SUP1
CC5       SM1
CC6       SM1
CC7       PM2
SUP1      PM1
PM1       AM1
SM1       PM2
PM2       AM1希望生成结果如下：AreaManager   ProjectManager   Supervisor   SiteManager   Staff
AM1           PM1              NULL          NULL         CC1
AM1           PM1              NULL          NULL         CC2
AM1           PM1              SUP1          NULL         CC3
AM1           PM1              SUP1          NULL         CC4
AM1           PM2              NULL          SM1          CC5
AM1           PM2              NULL          SM1          CC6
AM1           PM2              NULL          NULL         CC7
AM1           NULL             NULL          NULL         CC8
请各位指教！

Answer 1

if object_id('tb')is not null drop table tb
go
CREATE TABLE tb(员工编号 varchar(10), 领导编号 varchar(10))
INSERT tb SELECT
'CC8',      'AM1' UNION ALL SELECT 
'CC1',      'PM1' UNION ALL SELECT 
'CC2',      'PM1' UNION ALL SELECT 
'CC3',      'SUP1' UNION ALL SELECT 
'CC4',      'SUP1' UNION ALL SELECT 
'CC5',      'SM1' UNION ALL SELECT 
'CC6',      'SM1' UNION ALL SELECT 
'CC7',      'PM2' UNION ALL SELECT 
'SUP1',     'PM1' UNION ALL SELECT 
'PM1' ,     'AM1' UNION ALL SELECT 
'SM1' ,     'PM2' UNION ALL SELECT 
'PM2' ,     'AM1' ----------------------------------------------------------------------------
select 
AreaManager=case when 领导编号 like 'am%' then 领导编号 else '' end,
ProjectManager= case when 领导编号 like 'pm%' then 领导编号 else '' end,
Supervisor= case when 领导编号 like 'sup%' then 领导编号 else '' end,
SiteManager= case when 领导编号 like 'sm%' then 领导编号 else '' end,
Staff= 员工编号
into #t
from tb 
where  员工编号 like 'c%' 
order by   Staff 
update #t 
set Supervisor=case when SiteManager!='' then (select  case when 领导编号 like 'sup%' then 领导编号 else '' end from tb where 员工编号=SiteManager)
else Supervisor
    end
update #t 
set ProjectManager= case when  Supervisor!='' then (select  case when 领导编号 like 'pm%' then 领导编号 else '' end from tb where 员工编号=Supervisor)
 when SiteManager!='' then (select  case when 领导编号 like 'pm%' then 领导编号 else '' end from tb where 员工编号=SiteManager)
else ProjectManager 
    end 
update #t 
set AreaManager= case when ProjectManager!=''  then (select  case when 领导编号 like 'am%' then 领导编号 else '' end from tb where 员工编号=ProjectManager)
 when Supervisor!='' then (select  case when 领导编号 like 'am%' then 领导编号 else '' end from tb where 员工编号=Supervisor)
 when SiteManager!='' then (select  case when 领导编号 like 'am%' then 领导编号 else '' end from tb where 员工编号=SiteManager)
else AreaManager 
    endselect * from #t 
/*AreaManager ProjectManager Supervisor SiteManager Staff
----------- -------------- ---------- ----------- ----------
AM1         PM1                                   CC1
AM1         PM1                                   CC2
AM1         PM1            SUP1                   CC3
AM1         PM1            SUP1                   CC4
AM1         PM2                       SM1         CC5
AM1         PM2                       SM1         CC6
AM1         PM2                                   CC7
AM1                                               CC8(8 行受影响)
*/drop table #t