Sql – Grouping Hierarchical data (parentID+ID) and running sum

reporting-servicessqlssrs-2008

I have the following data:

ID    parentID    Text        Price
1                 Root
2     1           Flowers
3     1           Electro
4     2           Rose        10
5     2           Violet      5
6     4           Red Rose    12
7     3           Television  100
8     3           Radio       70
9     8           Webradio    90

I am trying to group this data with Reporting Services 2008 and have a sum of the price per group of level 1 (Flowers/Electro) and for level 0 (Root).

I have a table grouped on [ID] with a recursive parent of [parendID] and I am able to calculate the sum for the level 0 (just one more row in the table outside the group), but somehow I am not able to create sum's per group as SRSS does "create" groups per level. My desired result looks like so:

ID    Text        Price
1     Root
|2    Flowers
|-4   Rose        10
|-5   Violet      5
| |-6 Red Rose    12
|     Group Sum-->27
|3    Electro
|-7   Television  100
|-8   Radio       70
  |-9 Webradio    90
      Group Sum-->260
----------------------
Total             287

(indentation of ID just added for level clarification)

With my current approach I cannot get the group sums, so I figured out I would need the following data structure:

ID    parentID    Text        Price    level0    level1    level2    level3
1                 Root                 1
2     1           Flowers              1         1
3     1           Electro              1         2
4     2           Rose        10       1         1         1
5     2           Violet      5        1         1         2
6     4           Red Rose    12       1         1         1         1
7     3           Television  100      1         2         1
8     3           Radio       70       1         2         2
9     8           Webradio    90       1         2         2         1

When having the above structure I can create an outer grouping of level0, with child groupings level1, level2, level3 accordingly . When now having a "group sum" on level1, and the total sum outside the group I have EXACTLY what I want.

My question is the following:
How do I either achieve my desired result with my current data structure, or how do I convert my current data structure (outer left joins?) into the "new data structure" temporarily – so I can run my report off of the temp table?

Thanks for taking your time,
Dennis

Best Answer

WITH    q AS
        (
        SELECT  id, parentId, price
        FROM    mytable
        UNION ALL
        SELECT  p.id, p.parentID, q.price
        FROM    q
        JOIN    mytable p
        ON      p.id = q.parentID
        )
SELECT  id, SUM(price)
FROM    q
GROUP BY
        id

Update:

A test script to check:

DECLARE @table TABLE (id INT NOT NULL PRIMARY KEY, parentID INT, txt VARCHAR(200) NOT NULL, price MONEY)

INSERT
INTO @table
SELECT 1, NULL, 'Root', NULL
UNION ALL
SELECT 2, 1, 'Flowers', NULL
UNION ALL
SELECT 3, 1, 'Electro', NULL
UNION ALL
SELECT 4, 2, 'Rose', 10
UNION ALL
SELECT 5, 2, 'Violet', 5
UNION ALL
SELECT 6, 4, 'Red Rose', 12
UNION ALL
SELECT 7, 3, 'Television', 100
UNION ALL
SELECT 8, 3, 'Radio', 70
UNION ALL
SELECT 9, 8, 'Webradio', 90;

WITH    q AS
        (
        SELECT  id, parentId, price
        FROM    @table
        UNION ALL
        SELECT  p.id, p.parentID, q.price
        FROM    q
        JOIN    @table p
        ON      p.id = q.parentID
        )
SELECT  t.*, psum
FROM    (        
        SELECT  id, SUM(price) AS psum
        FROM    q
        GROUP BY
                id
        ) qo
JOIN    @table t
ON      t.id = qo.id

Here's the result:

1       NULL    Root            NULL    287,00
2       1       Flowers         NULL    27,00
3       1       Electro         NULL    260,00
4       2       Rose            10,00   22,00
5       2       Violet          5,00    5,00
6       4       Red Rose        12,00   12,00
7       3       Television      100,00  100,00
8       3       Radio           70,00   160,00
9       8       Webradio        90,00   90,00
Related Topic