--标准化科目表
select distinct 级次 from 用友科目表;
select
id as 科目ID,
accountingyear as 会计年,
code as 编号,
depth as 级次,
inId as 次级ID,
name as 名称
into 用友科目表
from AA_Account 科目DTO;
--标准化科目表_含上级
select * from 用友科目表_含上级;
select
c.科目ID,
c.会计年,
c.编号,
c.级次,
a.名称 as 一级科目,
case c.级次 when 2 then b.名称 when 3 then b.名称 else '' end as 二级科目,
case c.级次 when 3 then c.名称 else '' end as 三级科目
into 用友科目表_含上级
from 用友科目表 a
left join 用友科目表 b on left(b.编号,4)=a.编号 and b.会计年 = a.会计年
left join 用友科目表 c on left(c.编号,6)=b.编号 and c.会计年 = b.会计年
where c.科目ID is not null
order by 1;
--标准化科目表_含上级全称
select * from 用友科目表_含上级全称;
select *,
case 级次
when 1 then cast ([一级科目] as varchar)
when 2 then cast ([一级科目] as varchar) + '\'+cast ([二级科目] as varchar)
when 3 then cast ([一级科目] as varchar) + '\'+cast ([二级科目] as varchar) + '\'+cast ([三级科目] as varchar)
end
as 科目全称
into 用友科目表_含上级全称
from 用友科目表_含上级
order by 1;