疑难问题解答！（SQL语句）

Question

现有组织表如下：orgid orgname orgtype part_orgid
IT     IT部门   3       ST           
ST     XX集团   1       -
ST1    第1工厂  2      ST
ST2    第2工厂  2      ST
IT01   硬件科   4      IT
IT0201  软件班   5      IT
orgtype 1代表集团 2代表工厂 3代表 部门   4代表科  5代表班现有一需求:
生成如下格式：
集团ID, 集团名称,工厂ID，工厂名称，部门ID,部门名称，科ID ,科名称， 班ID, 班名称
ST      XX集团     ST1    第1工厂  
ST      XX集团                      IT     IT部门    IT01  硬件科 
ST      XX集团                      IT     IT部门                 IT0201  软件班
重点说明：IT部门的直接上级是集团,但由于他是部门，中间无工厂级别的，直接为空即可！
       即为：跨级别的中间组织则为空。CREATE TABLE t(
orgid varchar(20),orgname  varchar(30),orgtype  int,part_orgid VARCHAR(20))
INSERT INTO t
SELECT 'IT','IT部门',3,'ST' union all           
SELECT'ST' ,   ' XX集团'  , 1  ,     '-'union all       
SELECT'ST1'  ,  '第1工厂' , 2 ,     'ST'union all       
SELECT'ST2' ,   '第2工厂' , 2 ,     'ST'union all       
SELECT'IT01' ,  '硬件科' ,  4 ,     'IT'union all       
SELECT 'IT0201',  '软件班',   5  ,  'IT'

Answer 1

变样了!应该是这样的.结果如下:
/*
集团ID, 集团名称,工厂ID，工厂名称，部门ID,部门名称，科ID ,科名称， 班ID, 班名称
ST XX集团         ST1     第1工厂   
ST XX集团                            IT     IT部门  IT01  硬件科  
ST XX集团                            IT      IT部门                IT0201 软件班*/

Answer 2

--还是感觉有点别扭 select 
isnull(t5.orgid,t1.集团) as 集团ID, isnull(t5.orgname,t1.集团名称) as 集团名称,
isnull(t4.orgid,t1.工厂) as 工厂ID, isnull(t4.orgname,t1.工厂名称) as 工厂名称,
isnull(t3.orgid,t1.部门) as 部门ID, isnull(t3.orgname,t1.部门名称) as 部门名称,
isnull(t2.orgid,t1.科) as 科ID,isnull(t2.orgname,t1.科名称) as 科名称,
t1.班 as 班ID, t1.班名称
from
(
select 
case orgtype when 1 then orgid else '' end  as 集团,
case orgtype when 1 then orgname else '' end  as 集团名称,
case orgtype when 2 then orgid else '' end  as 工厂,
case orgtype when 2 then orgname else '' end  as 工厂名称,
case orgtype when 3 then orgid else '' end  as 部门,
case orgtype when 3 then orgname else '' end  as 部门名称,
case orgtype when 4 then orgid else '' end  as 科,
case orgtype when 4 then orgname else '' end  as 科名称,
case orgtype when 5 then orgid else '' end  as 班,
case orgtype when 5 then orgname else '' end  as 班名称
from #t a
where not exists(select 1 from #t where part_orgid= a.orgid)
)t1 
left join (select * from #t where orgtype = 4) t2 
on t2.orgid = (select part_orgid from #t where orgid = t1.班)
left join (select * from #t where orgtype = 3) t3 
on t3.orgid = (select part_orgid from #t where orgid = t1.班 or orgid = t1.科)
 or t3.orgid = (select part_orgid from #t where orgid = t2.orgid)
left join (select * from #t where orgtype = 2) t4 
on t4.orgid = (select part_orgid from #t where orgid = t1.班 or orgid = t1.科 or orgid = t1.部门)
 or t4.orgid = (select part_orgid from #t where orgid = t2.orgid)
 or t4.orgid = (select part_orgid from #t where orgid = t3.orgid)
left join (select * from #t where orgtype = 1) t5 
on t5.orgid = (select part_orgid from #t where orgid = t1.班 or orgid = t1.科 or orgid = t1.部门 or orgid = t1.工厂)
 or t5.orgid = (select part_orgid from #t where orgid = t2.orgid)
 or t5.orgid = (select part_orgid from #t where orgid = t3.orgid)
 or t5.orgid = (select part_orgid from #t where orgid = t4.orgid)
--结果
/*
ST  XX集团 ST1 第1工厂
ST  XX集团 ST2 第2工厂
ST  XX集团 IT IT部门 IT01 硬件科
ST  XX集团 IT IT部门 IT0201 软件班
*/

Answer 3

CREATE TABLE t(
orgid varchar(20),orgname  varchar(30),orgtype  int,part_orgid VARCHAR(20))
INSERT INTO t
SELECT 'IT','IT部门',3,'ST' union all           
SELECT'ST' ,   ' XX集团'  , 1  ,     '-'union all       
SELECT'ST1'  ,  '第1工厂' , 2 ,     'ST'union all       
SELECT'ST2' ,   '第2工厂' , 2 ,     'ST'union all       
SELECT'IT01' ,  '硬件科' ,  4 ,     'IT'union all       
SELECT 'IT0201',  '软件班',   5  ,  'IT'/*
集团ID, 集团名称,工厂ID，工厂名称，部门ID, 部门名称， 科ID , 科名称，  班ID,   班名称
ST XX集团         ST1     第1工厂   
ST XX集团                            IT     IT部门   IT01   硬件科  
ST XX集团                            IT     IT部门                IT0201   软件班*/
规律倒是不乱.