SQL SERVER implements grouping merge to implement column data stitching

Requirements scenario: The data structure of an organization in SQL SERVER is a hierarchical relationship that now needs to capture all of the organizational information above each organization node, with the following examples:

adorg_id--------------ParentID-----------------shortname

001 Top Organization Name

001.021 level 12 Organization name

001.022 001.021 Third-level organization name

001.021.211 001.022 level Four organization name

001.023 001.021 Third-level organization name

The results you need to get now are:

adorg_id--------------OrgName------------------------------------------shortname

001 Top Organization Name top-level organization name

001.021 Top organization name, secondary organization name second-level organization name

001.022 Top-level organization name, secondary organization name, third-level organization name three-level organization name

001.021.211 Top organization name, second-level organization name, third-level organization name, level four organization name four-level organization name

001.023 Top-level organization name, secondary organization name, third-level organization name three-level organization name

SQL--------------------------------

DECLAREOrgcursor  for SelectAdorg_id,shortname fromadorganizeDECLARE @ORGID nvarchar( -) DECLARE @ORGSHORTNAME nvarchar( -) DECLARE @RESULT Table([ID] nvarchar( -)NULL,[OrgInfo] nvarchar(MAX)NULL,[ShortName] nvarchar( -)NULL ) Openorg;FETCH NEXT  fromOrg into @ORGID,@ORGSHORTNAME  while @ @FETCH_STATUS=0 BEGIN  withNode as(Select *  fromAdorganize (NOLOCK)whereadorg_id=@ORGID Union  All SELECTPar.*  fromAdorganize (NOLOCK) asParINNER JOINNode asRC onPar. adorg_id=RC. ParentID)INSERT  into @RESULT SELECT @ORGID, ShortName,@ORGSHORTNAME  fromAdorganize (NOLOCK)WHEREadorg_idinch(SELECTadorg_id fromnode N)FETCH NEXT  fromORG into @ORGID,@ORGSHORTNAME END ; CLOSEorg;deallocateorg;SELECTID, data=Stuff((SELECT ','+OrgInfo from @RESULTTWHEREId=T1.id forXML PATH ("')),1,1,"'), ShortName from @RESULTT1GROUP  byId,shortname

Description: Because there is no good way to think of the data directly after the query processing, stored in a temporary table, and then the temporary table data grouping, using stuff to splice the data to splice, and then output. Don't know if there's a better way, welcome to discuss ~~~~~~~~~

SQL SERVER implements grouping merge to implement column data stitching