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