Requête liée à l'ordre hiérarchique et à l'espace indenté

vous avez besoin d'une database récursive pour get des données dans la hiérarchie,

 -- populate test data DECLARE @tbl TABLE (Child VARCHAR(100), Parent VARCHAR(100), Level INT, LevelName VARCHAR(100)) INSERT INTO @tbl VALUES ('AllRegion' ,'AllRegion' ,1 ,'national') ,('AfricaRegion' ,'AllRegion' ,2 ,'region') ,('America' ,'AllRegion' ,2 ,'region') ,('Asia' ,'AllRegion' ,2 ,'region') ,('EuropeRegion' ,'AllRegion' ,2 ,'region') ,('Africa' ,'AfricaRegion' ,3 ,'Subregion') ,('AsiaPacific' ,'Asia' ,3 ,'Subregion') ,('Europe' ,'EuropeRegion' ,3 ,'Subregion') ,('NorthAmerica' ,'America' ,3 ,'Subregion') ,('SouthAmerica' ,'America' ,3 ,'Subregion') ,('Argentina' ,'SouthAmerica' ,4 ,'Country') ,('Australia' ,'AsiaPacific' ,4 ,'Country') ,('Pakistan' ,'AsiaPacific' ,4 ,'Country') ,('SouthAfrica' ,'Africa' ,4 ,'Country') ,('Tunisia' ,'Africa' ,4 ,'Country') ,('Uruguay' ,'SouthAmerica' ,4 ,'Country') -- use this query ;WITH Cte AS ( SELECT * ,ROW_NUMBER() OVER (PARTITION BY t.LevelName, t.Parent ORDER BY t.Level) AS RowNo -- assing a number to use for sorting from @tbl t ), Final AS ( SELECT t.child, t.Parent, t.Level ,CAST(RowNo AS VARCHAR(MAX)) AS SortBy -- this will be used for sorting. FROM Cte t WHERE t.level = 1 UNION ALL SELECT c.child, c.parent, c.level ,CAST(p.SortBy + CAST(c.RowNo AS VARCHAR(MAX))AS VARCHAR(MAX)) AS SortBy -- keep adding the levels to the sort order FROM Cte c INNER JOIN Final p On c.Parent = p.child AND c.Level > 1 -- this is required as the top parent is not set as NULL ) SELECT Child ,SortBy ,SPACE(Level * 4 ) + Child AS HierarcyText FROM Final ORDER BY SortBy OPTION(MAXRECURSION 0) 

J'espère que cela vous aide.

résultat :

 Child SortBy HierarcyText ----------------- ------- ----------------------------- AllRegion 1 AllRegion AfricaRegion 11 AfricaRegion Africa 111 Africa SouthAfrica 1111 SouthAfrica Tunisia 1112 Tunisia America 12 America NorthAmerica 121 NorthAmerica SouthAmerica 122 SouthAmerica Uruguay 1221 Uruguay Argentina 1222 Argentina Asia 13 Asia AsiaPacific 131 AsiaPacific Australia 1311 Australia Pakistan 1312 Pakistan EuropeRegion 14 EuropeRegion Europe 141 Europe 

 select case when ROW_NUMBER() OVER(ORDER BY r2.levelname DESC) = 1 then r1.levelname else '' end as L1, case when ROW_NUMBER() OVER(PARTITION BY r2.Child ORDER BY r2.Child DESC) = 1 then r2.Child else '' end as L2, case when ROW_NUMBER() OVER(PARTITION BY r3.Child ORDER BY r3.Child DESC) = 1 then r3.Child else '' end as L3, case when ROW_NUMBER() OVER(PARTITION BY r4.Child ORDER BY r4.Child DESC) = 1 then r4.Child else '' end as L4 from #Regions r1 inner join #Regions r2 on r1.Child = r2.Parent and r2.level = 2 inner join #Regions r3 on r2.Child = r3.Parent and r3.level = 3 inner join #Regions r4 on r3.Child = r4.Parent and r4.level = 4 where r1.level = 1 order by r1.levelname, r2.Child, r3.Child, r4.Child 

1. Enregistrer les résultats ci-dessous dans la table temporaire
2. Ajouter à tous les tabs de valeurs non vides (CHAR (9)), CRLF (CHAR (13) + CHAR (10))
3. Combinez tout en une seule string
4. Imprimer la string

Je n'ai fait que l'étape (1), les étapes (2) – (4) sont faciles. Faites-moi savoir si ce n'est toujours pas clair.

Une requête CTE récursive SQL peut aider à définir des structures de hiérarchie dans SQL Server

Voici comment vous pouvez utiliser une requête récursive dans SQL

 ;with cte as ( select Child, Parent, Level, LevelName, rn = CONVERT(nvarchar(max), ROW_NUMBER() Over (Partition By Level Order By Child) ) from Regions where Level = 1 union all select r.Child, r.Parent, r.Level, r.LevelName, rn = rn + '_' + CONVERT(nvarchar(max), ROW_NUMBER() Over (Partition By r.Level Order By r.Child) ) from Regions r inner join cte on r.Level = (cte.Level + 1) and r.Parent = cte.Child ) select * from cte order by rn