Toronto, Ontario, Canada

Eastern Time Zone

info@maximconsulting.ca

Maxim Consulting International Inc.

Quick and simple aggregation of Hierarchical Data in SQL Server

Archive

Categorise

Recent Posts

Maxim Smirnov

Sometimes data containing hierarchies have to be aggregated and total calculated on different levels. The following script will help archive fast calculation of totals on each level of hierarchy.

INPUT DATA STRUCTURE AND VALUES

0100***Level 1*****$21
0110*****Level 2***$6
0111******Level 3**$1
0112******Level 3**$2
0113******Level 3**$3
0120*****Level 2***$15
0121******Level 3**$4
0122******Level 3**$5
0123******Level 3**$6


— define codes table with hierarchy

 Declare @Code Table (Code varchar(10), Hierarchy varchar(50), Description varchar(100))

 INSERT @Code (Code, Hierarchy, Description) VALUES (‘0100’, ‘1.’, ‘Total’)
 INSERT @Code (Code, Hierarchy, Description) VALUES (‘0110’, ‘1.01.’, ‘Total of 0110’)
 INSERT @Code (Code, Hierarchy, Description) VALUES (‘0111’, ‘1.01.01.’, ‘Value of 0111’)
 INSERT @Code (Code, Hierarchy, Description) VALUES (‘0112’, ‘1.01.02.’, ‘Value of 0112’)
 INSERT @Code (Code, Hierarchy, Description) VALUES (‘0113’, ‘1.01.03.’, ‘Value of 0113’)
 INSERT @Code (Code, Hierarchy, Description) VALUES (‘0120’, ‘1.02.’, ‘Total of 0120’)
 INSERT @Code (Code, Hierarchy, Description) VALUES (‘0121’, ‘1.02.01.’, ‘Value of 0121’)
 INSERT @Code (Code, Hierarchy, Description) VALUES (‘0122’, ‘1.02.02.’, ‘Value of 0122’)
 INSERT @Code (Code, Hierarchy, Description) VALUES (‘0123’, ‘1.02.03.’, ‘Value of 0123’)

— create table to record values for codes

 Declare @CodeValues Table (Code varchar(10), Value money)

 INSERT @CodeValues (Code, Value) VALUES (‘0111’, 1)
 INSERT @CodeValues (Code, Value) VALUES (‘0112’, 2)
 INSERT @CodeValues (Code, Value) VALUES (‘0113’, 3)
 INSERT @CodeValues (Code, Value) VALUES (‘0121’, 4)
 INSERT @CodeValues (Code, Value) VALUES (‘0122’, 5)
 INSERT @CodeValues (Code, Value) VALUES (‘0123’, 6)

— create temp table that will combine values with code hierarcies and will be used in the rollup

 Declare @TmpValues Table (Code varchar(10), CodeHierarchy varchar(10), Value money)

 INSERT @TmpValues (Code, Value, CodeHierarchy)
 Select
   v.Code,
   v.Value,
   c.Hierarchy
 From
   @CodeValues v
   Inner Join @Code c On c.Code = v.Code

— Rollup Data

 Select
  c.Code,
  c.Description,
  Sum(v.Value)
 From
  @TmpValues v
  Join @Code c On v.CodeHierarchy Like c.Hierarchy+’%’
 Group By
  c.Code,
  c.Description
 Order By
  c.Code

This method was originally suggested by Scot Smith, Toronto.

,