Referring to an earlier problem statement at: SQL Server Hierarchical Sum of column
Code below for the implementation:
USE tempdb;
IF OBJECT_ID('dbo.Hierarchy') IS NOT NULL
DROP TABLE dbo.[Hierarchy];
CREATE TABLE dbo.Hierarchy
(
ID INT NOT NULL PRIMARY KEY,
ParentID INT NULL,
CONSTRAINT [FK_parent] FOREIGN KEY ([ParentID]) REFERENCES dbo.Hierarchy([ID]),
hid HIERARCHYID,
Amount INT
);
INSERT INTO [dbo].[Hierarchy]
( [ID], [ParentID], [Amount] )
VALUES
(1, NULL, NULL ),
(2, 1, NULL),
(3, 1, 50),
(4, 2, 58),
(5, 2, 7),
(6, 3, 10),
(7, 3, 20)
; WITH cte AS (
SELECT [h].[ID] ,
[h].[ParentID] ,
CAST('/' + CAST(h.[ID] AS VARCHAR(10)) + '/' AS VARCHAR(MAX)) AS [h],
[h].[hid]
FROM [dbo].[Hierarchy] AS [h]
WHERE [h].[ParentID] IS NULL
UNION ALL
SELECT [h].[ID] ,
[h].[ParentID] ,
CAST([c].[h] + CAST(h.[ID] AS VARCHAR(10)) + '/' AS VARCHAR(MAX)) AS [h],
[h].[hid]
FROM [dbo].[Hierarchy] AS [h]
JOIN [cte] AS [c]
ON [h].[ParentID] = [c].[ID]
)
UPDATE [h]
SET hid = [cte].[h]
FROM cte
JOIN dbo.[Hierarchy] AS [h]
ON [h].[ID] = [cte].[ID];
SELECT p.id, SUM([c].[Amount])
FROM dbo.[Hierarchy] AS [p]
JOIN [dbo].[Hierarchy] AS [c]
ON c.[hid].IsDescendantOf(p.[hid]) = 1
GROUP BY [p].[ID];
Thanks to Ben Thul for the above code! I have modified it slightly to suit my problem statement. As can be seen the nodes with ID=1 and 2 do not have any values. The same in pictorial representation is below. I am not allowed to post inline pictures yet.
link here or https://i.stack.imgur.com/RuBdu.png
The scenario I have is since node with ID=3 has a value I want it's parent to get the summed value of nodes with ID 2 and 3. What the below code does is it bubbles the grandchildren node values as well.
Current Output:
<style>
table, th, td {
border: 1px solid black;
}
</style>
<table>
<tr>
<th>ID</th>
<th>Value</th>
</tr>
<tr>
<td>1</td>
<td>145</td>
</tr>
<tr>
<td>2</td>
<td>65</td>
</tr>
<tr>
<td>3</td>
<td>80</td>
</tr>
<tr>
<td>4</td>
<td>58</td>
</tr>
<tr>
<td>5</td>
<td>7</td>
</tr>
<tr>
<td>6</td>
<td>10</td>
</tr>
<tr>
<td>7</td>
<td>20</td>
</tr>
</table>
Expected Output:
<style>
table {
border-collapse: collapse;
}
td, th {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
</style>
<table>
<tr>
<th>ID</th>
<th>Value</th>
</tr>
<tr>
<td>1</td>
<td style="background-color:#FF0000;">115</td>
</tr>
<tr>
<td>2</td>
<td>65</td>
</tr>
<tr>
<td>3</td>
<td style="background-color:#FF0000;">50</td>
</tr>
<tr>
<td>4</td>
<td>58</td>
</tr>
<tr>
<td>5</td>
<td>7</td>
</tr>
<tr>
<td>6</td>
<td>10</td>
</tr>
<tr>
<td>7</td>
<td>20</td>
</tr>
</table>
I hope this is clear to what I need. Thanks for the help!