SQLTeam.com | Weblogs | Forums

Hierarchy with Aggregation

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

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;

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.

one thing you can do is

isnull(column,'something')

so that it includes it