Grouping Hierarchical data (parentID+ID) and running sum?

Question

Hello,

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

Answer 1

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

Answer 2

I found a really ugly way to do what I want - maybe there is something better?

SELECT A.Text, A.Price,                   
  CASE
    WHEN D.Text IS NULL
    THEN
        CASE
            WHEN C.Text IS NULL
            THEN
                CASE
                    WHEN B.Text IS NULL
                    THEN
                        A.ID
                    ELSE B.ID
                END
            ELSE C.ID
            END
    ELSE D.ID
  END
  AS LEV0,

  CASE
    WHEN D.Text IS NULL
    THEN
        CASE
            WHEN C.Text IS NULL
            THEN
                CASE
                    WHEN B.Text IS NULL
                    THEN
                        NULL
                    ELSE A.ID
                END
            ELSE B.ID
            END
    ELSE C.ID
  END
  AS LEV1,

  CASE
    WHEN D.Text IS NULL
    THEN
        CASE
            WHEN C.Text IS NULL
            THEN
                NULL
            ELSE A.ID
            END
    ELSE B.ID
  END
  AS LEV2,

  CASE
    WHEN D.Text IS NULL
    THEN NULL
    ELSE A.ID
  END
  AS LEV3

FROM         dbo.testOld AS A LEFT OUTER JOIN
                  dbo.testOld AS B ON A.parentID = B.ID LEFT OUTER JOIN
                  dbo.testOld AS C ON B.parentID = C.ID LEFT OUTER JOIN
                  dbo.testOld AS D ON C.parentID = D.ID

Output of this is:

Text       Price       LEV0        LEV1        LEV2        LEV3
---------- ----------- ----------- ----------- ----------- -----------
Root       NULL        1           NULL        NULL        NULL
Flowers    NULL        1           3           NULL        NULL
Electro    NULL        1           4           NULL        NULL
Television 100         1           4           5           NULL
Radio      70          1           4           6           NULL
Rose       10          1           3           7           NULL
Violet     5           1           3           8           NULL
Webradio   90          1           4           5           14
Red Rose   12          1           3           7           15

With this structure I can go ahead and create 4 nested groups on the LEV0-3 columns including subtotals per group (as shown above in my desired result).