Zahid Zahid - 4 months ago 12
SQL Question

Query related to hierarchy order and indented space

Input Table : Regions

+---------------+---------------+---------- +-----------+
| Child | Parent | Level | levelname|
+---------------+---------------+---------- +-----------+
| All Region | All Region | 1 | national |
| Africa Region | All Region | 2 | region |
| America | All Region | 2 | region |
| Asia | All Region | 2 | region |
| Europe Region | All Region | 2 | region |
| Africa | Africa Region | 3 | Subregion |
| Asia Pacific | Asia | 3 | Subregion |
| Europe | Europe Region | 3 | Subregion |
| North America | America | 3 | Subregion |
| South America | America | 3 | Subregion |
| Argentina | South America | 4 | Country |
| Australia | Asia Pacific | 4 | Country |
| Pakistan | Asia Pacific | 4 | Country |
| South Africa | Africa | 4 | Country |
| Tunisia | Africa | 4 | Country |
| Uruguay | South America | 4 | Country |
+-------------------------------------------------------+


Here , regions are of 4 levels


  • All region

  • Region

  • Sub Region

  • Country



. The output should be as follows:

National
Region_1
SubRegion_1_1
Country_1_1_1
Country_1_1_2
SubRegion_1_2
Country_1_2_1
Country_1_2_2
Country_1_2_3
Country_1_2_4
Region_2
…. And so on.


The output has some indented spaces to show visual effects,I dont know how to start the query.

Answer

you need recursive cte to get data in hierarchy,

-- 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)

Hope this helps you.

result :

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