SQL Server sorting in the tree structure department

SQL Server sorting in the tree structure department

Because you want to implement the departmental sorting function, and to consider the departmental hierarchy, it is not possible to sort directly with SQL, so write a SQL function to support it.
First Department table: Company

CREATE TABLE Company (CompanyID           ID         notNULL, CompanyName         nvarchar( +)     not NULL )

A table that records departmental hierarchies that are not recorded in this table if the department does not have a parent department :

CREATE TABLE Company_report (companyid     ID    notNULL, reporttoid    ID    not  NULL, Displayord    ord   CONSTRAINT[Df1_company_ Report]DEFAULT (1notNULL)

In Company_report, reporttoid refers to the CompanyID of the superior department.
Like this tree structure, in the code is generally used recursive return traversal, but in SQL to implement recursive return is very troublesome, or write a simple loop point.
Define function:

Goif(exists(Select *  fromSys.objectswhereName= 'GET_COMPANY_REPORT_NAME_FN'))Drop FUNCTIONGET_COMPANY_REPORT_NAME_FNGoCREATE FUNCTIONGET_COMPANY_REPORT_NAME_FN (@i_vCompanyIdId@i_vCompanyNamestring2)RETURNSstring2 asBEGIN   DECLARE @t_vResultstring2; DECLARE @t_vReportToIdID; SET @t_vResult = "'; --Parent Department ID   SET @t_vReportToId = 0; --Stitching Parent Department Name   SELECT @t_vResult =R.companyname+ '_' +C.companyname,@t_vReportToId =Cr. Reporttoid fromCompany_report CR, Company C, company RWHERECr.companyid=C.companyid andCr. Reporttoid=R.companyid andCr.companyid= @i_vCompanyId   --While parent department also exists parent department    while (       exists(SelectCr. Reporttoid fromCompany_report CRwhereCr.companyid= @t_vReportToId)   )   begin        SELECT @t_vResult =R.companyname+ '_'+  @t_vResult,         @t_vReportToId =Cr. Reporttoid fromCompany_report CR, Company C, company RWHERECr.companyid=C.companyid andCr. Reporttoid=R.companyid andCr.companyid= @t_vReportToId   End   --It's already the top-level department. Return original value   if @t_vResult = "'   begin      SET @t_vResult = @i_vCompanyName   End   return @t_vResultENDGO

The principle is that the name of the child department is added to the name of the parent department and the parent department continues to connect if the parent department still exists.
This is called when sorting:

Select  from Order  by Dbo.get_company_report_name_fn (CompanyID, CompanyName)

Results:

ula-client01 ltd.ula-client01 ltd._ula-client02ula-client01 ltd._ula-client02_ula-client02-son ula-client01 LTD._ Ula-client03sonysony_hairsony_hair_ibm

After writing to Leader to see, he felt that I wrote complex, and then readily changed:

Goif(exists(Select *  fromSys.objectswhereName= 'GET_COMPANY_REPORT_NAME_FN'))Drop FUNCTIONGET_COMPANY_REPORT_NAME_FNGoCREATE FUNCTIONGET_COMPANY_REPORT_NAME_FN (@i_vCompanyIdId@i_vCompanyNamestring2)RETURNSstring2 asBEGIN   DECLARE @t_vResultstring2; DECLARE @t_vReportToIdID; DECLARE @t_vReportToNamestring2; SET @t_vResult = @i_vCompanyName; SET @t_vReportToId = @i_vCompanyId;  while(exists(SelectCr. Reporttoid fromCompany_report CRwhereCr.companyid= @t_vReportToId))   begin      SELECT @t_vReportToId =Cr. Reporttoid,@t_vReportToName =C.companyname fromCompany_report CR, Company CWHERECr. Reporttoid=C.companyid andCr.companyid= @t_vReportToId; Set @t_vResult = @t_vReportToName + '_' + @t_vResult; End   return @t_vResult; ENDGo

Well, it's a lot easier. The main is to eliminate the duplicated code.
END.

SQL Server sorting in the tree structure Department