Tsql tree data processing (updated version)

compared with the previous version, the field is slightly different. If the field is changed to a long character. The sample data file cannot be uploaded directly, so it is a compressed package file.

Alter function ufn_id () -- because the fields in the tree data table to be sorted are encoded in bytes type and the maximum length is 20 bits, the fields corresponding to the virtual table in the function are longer. Returns @ Re table (ID varchar (200), -- current node encoding PID varchar (200), -- upper node encoding level int, -- level difference Sid varchar (8000 ), -- Sort encoding difference code pre1 varchar (2), -- node ID pre2 varchar (2) -- node ID) as begin -- Sort encoding length: Get the length of the current field value, or the maximum length ??? The maximum value is used to unify the number of supplementary digits for easy sorting. declare @ Len intset @ Len = (select max (LEN (CODE) from DBO. zldept) Declare @ l int set @ l = 0 insert @ Re Select Code, upcode, @ l, right ('20140901' + code, @ Len ),'', ''from DBO. zldept where upcode = ''while @ rowcount> 0 begin set @ l = @ L + 1 insert @ Re select. code,. upcode, @ L, B. sid + ',' + right ('000000' +. code, @ Len), '','' from DBO. zldept A, @ Re B where B. id =. upcode and B. level = @ L-1 end update aset pre2 = case when. level> 1 then case ID when (select right (max (SID), max (LEN (ID) from @ Re where pid =. PID and level =. level) Then 'hour' else' else 'end else case ID when (select right (max (SID), max (LEN (ID) from @ Re where level =. level) Then 'elastic' then 'endendfrom@re awhere. level> 0 update aset pre1 = case when pid = (select right (max (SID), max (LEN (ID) from @ Re where level =. level-1) Then ''else' │ 'endfrom @ Re awhere. level> 1 returnend -- ====================================== ==================== select [name with node id] = case when B. [level] = 0 then. name When B. [level] = 1 then B. pre2 + replicate ('-', 4) +. name else replicate (B. pre1 + replicate ('', (B. level-1) * 4), B. [level]-1) + B. pre2 + replicate ('-', 4) +. name end,. name,. codefrom zldept A, ufn_id () B where. code = B. id order by B. sid