数据处理4位树代码扩展为6位

原始数据

1?? ?行政后勤?? ?????????0001
2?? ?院办?? ??? ?????? ????000104810002
4?? ?宣传室?? ??? ?????????000104810004
5?? ?综合档案室?? ?????????000104810005
6?? ?收发室?? ??? ?????????000104810006
7?? ?车队?? ??? ?????????000104810007
8?? ?人力资源部?? ?????00010008
10?? ?医院控制处?? ?????00010010
11?? ?病案管理处?? ?????00010011
12?? ?护理部?? ??? ?????00010012
13?? ?消毒供应中心?? ?01720013
14?? ?科教处?? ??? ?????00010014
16?? ?学院办公室?? ?????00010016
17?? ?监察审计处?? ?????00010017
18?? ?工会?? ??? ?????????00010018
19?? ?团委?? ??? ?????????00010019
20?? ?财务处?? ??? ?????????00010020
22?? ?附二门诊收费处?? ?000100200022
23?? ?儿童门急诊?? ?????????000100200023
24?? ?附二住院收费处?? ?000100200024
27?? ?儿童住院收费处?? ?000100200027

方法一：

;with tree as
(
   select ksdm,ksmc,case len(kswm) when 4 then '' when 8 then '00'+substring(kswm,1,4) when 12 then '00'+substring(kswm,5,4) when 16 then '00'+substring(kswm,9,4) end as p,case len(kswm) when 4 then '00'+kswm when 8 then '00'+substring(kswm,13,4) end as c 
    from ks
),cte as
(
	-->begin定位点
   select ksdm,c as kswm,c,p,1 as levle from tree where p=''
    -->end
   union all
    -->begin
   select tree.ksdm,tree.ksmc,cte.kswm+tree.c as kswm,tree.c,tree.p,levle+1 as levle from tree inner join cte on tree.p=cte.c 
    -->end
)
select * from cte order by kswm

Excel函数

=IF(LEN(C3)>4,CONCATENATE("00",MID(C3,4)),"")