Data processing 4-bit tree code expanded to 6-bit

Raw Data

1 Administrative Logistics 0001
2 Hospital Office000104810002
4 Propaganda Room 000104810004
5 General Registry 000104810005
6 mailroom 000104810006
7 Fleet 000104810007
8 Human Resources Department 00010008
10 Hospital Control Office 00010010
11 Medical records Management Office 00010011
12 Department of Nursing 00010012
13 Disinfection Supply Center 01720013
14 Science and Education Office 00010014
16 College Office 00010016
17 Audit Office 00010017
18 Trade Unions 00010018
19 Youth League 00010019
20 Finance Office 00010020
22 with two outpatient service charge 000100200022
23 Children's Gate emergency 000100200023
24 with two inpatient charge 000100200024
27 Child hospitalization Charge 000100200027

method One:

With the tree as (   select Ksdm,ksmc,case len (KSWM) When 4 then "when 8 Then '" +substring (kswm,1,4) when the then ' xx ' +s Ubstring (kswm,5,4) when the then ' xx ' +substring (kswm,9,4) end as P,case Len (KSWM) when 4 Then ' xx ' +kswm when 8 then ' xx ' +su Bstring (kswm,5,4) when the then ' xx ' +substring (kswm,9,4) when the then ' xx ' +substring (kswm,13,4) end as C from     KS), CTE a S (-->begin locator point   Select Ksdm,ksmc,c as kswm,c,p,1 as Levle from tree where p= "    -->end   UNION ALL    --& Gt;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 O n tree.p=cte.c     -->end) SELECT * from the CTE ORDER by KSWM

Method Two:

Excel functions

=if (LEN (C3) >4,concatenate ("xx", MID (c3,5,4)), "")

Data processing 4-bit tree code expanded to 6-bit