生成报表的相关sql

一张(l 和 h是同一张表,起了不同的别名)保存了名称,机构编码,上级编码的机构编码表,通过sql语句进行加工,得到一张报表的行数据(横向多排),别且根据数据条件形成不同层次.

select code1, name1, name2
from ((select h.org||'00' code1,
l.name || ' ' name1,
h.name name2
from l
join h
on (l.org = h.orgtype)
where l.m=1 AND l.o = decode([CODE],'000000', '-1', [CODE])) union all
(select [CODE]||'00', '总计', '总计'
from dual
union all
select l.org||'00' code1,
l.name || ' ' name1,
'合计'||getNbsp(rownum) name2
from l
where l.m=1 AND l.org <> '000000'
and l.o = decode([CODE],'000000', '-1', [CODE]))
union all
select l.org||'01' code1,
l.name || ' ' name1,
l.name||decode([CODE],'000000','(责任单位)','(合作单位)') name2
from l
where l.m=1 AND l.o = decode([CODE],'000000', '-1',[CODE])
and l.org<>'000000'
union all

select l.org||'02' code1,
l.name||'(责任单位)' name1,
'合计' name2
from l
where l.mgrtype=1 AND l.org = decode([CODE],'000000', '-1',[CODE])
)
order by code1