Hierarchy with Aggregation

Transact-SQL
1

Hi,

I have a requirement to apply aggregation for each levels of hierarchy.
This can be done in separate query with a loop - but is there a way to make this in a single SQL query.

Here Levels can be upto 2 or even upto 5..

TableA:
Columns:
Brand, H1, H2, H3, H4, H5, Price
Values:
Brand1, Base, Retail, dist,null,null, 50
Brand1, Base, Retail, dist1, null,null, 25
Brand1, Base, Retail, dist3, null,null,40
Brand1, Base, Others, OOH1, null,null,40
Brand1, Base, Others, OOH2, null,null,30

Output:
Columns:
Brand, Level, Hierarchy, Price
Values:
Brand1, level1, Base, 185
Brand1, Level2, Base_Retail, 115
Brand1,Level2, Base_Othes, 70
Brand1,Level3, Base_Retail_dist, 50
Brand1,Level3, Base_Retail_dist1, 25
Brand1,Level3, Base_Retail_dist3, 40
Brand1,Level3, Base_Retail_OOH1, 40
Brand1,Level3, Base_Retail_OOH2, 30

2

please take this as an example

; WITH RECURSIVE folder_hierarchy AS (
  SELECT    
      id,
      name,
      subfolder_of,
      CAST (name AS text) AS path
  FROM 
     folder
  WHERE 
     subfolder_of IS NULL
     
  UNION ALL
     
  SELECT    folder.id,
            folder.name,
            folder.subfolder_of,
            folder_hierarchy.path || '\' || folder.name
  FROM 
       folder, folder_hierarchy
  WHERE 
     folder.subfolder_of = folder_hierarchy.id
)
SELECT 
   *
FROM 
  folder_hierarchy;
3

Hi Harrish,
Am not getting the right result.

One change with my Query - In my example have listed the Columns, But if any column is null that wont be available in Table. So its only H1, H2 and H3 available in table.

4

one thing you can do is

isnull(column,'something')

so that it includes it