SQL Server CTE to aggregate costs at each level of the hierarchy
I cannot figure out how to write a CTE that will roll up the costs from the transaction table to the self-join table so that it gives me the total costs at each level of the hierarchy. I put together a very simple example to illustrate the problem. Here are the DDL and Insert scripts so you can reproduce the problem if you would be so kind as to help me:
CREATE TABLE [Items](
[ItemId] [int] IDENTITY(1,1) NOT NULL,
[ParentId] [int] NULL,
[ItemName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED
(
[ItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Transactions](
[TransactionId] [int] IDENTITY(1,1) NOT NULL,
[ItemId] [int] NOT NULL,
[Amount] [money] NOT NULL,
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[TransactionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [Items] ON
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (1, NULL, N'Warehouse')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (3, 1, N'Bin 1')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (4, 1, N'Bin 2')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (5, 3, N'Item 1.1')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (6, 3, N'Item 1.2')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (7, 4, N'Item 2.1')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (8, 4, N'Item 2.2')
GO
SET IDENTITY_INSERT [Items] OFF
GO
SET IDENTITY_INSERT [Transactions] ON
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (1, 5, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (2, 5, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (3, 6, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (4, 6, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (5, 4, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (6, 7, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (7, 7, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (8, 8, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (9, 8, 10.0000)
GO
SET IDENTITY_INSERT [Transactions] OFF
GO
ALTER TABLE [Items] WITH CHECK ADD CONSTRAINT [FK_Items_Items] FOREIGN KEY([ParentId])
REFERENCES [Items] ([ItemId])
GO
ALTER TABLE [Items] CHECK CONSTRAINT [FK_Items_Items]
GO
ALTER TABLE [Transactions] WITH CHECK ADD CONSTRAINT [FK_Transactions_Items] FOREIGN KEY([ItemId])
REFERENCES [Items] ([ItemId])
GO
ALTER TABLE [Transactions] CHECK CONSTRAINT [FK_Transactions_Items]
GO
Here is the CTE I was working on:
With cteAggregateCost
as
(
select i.itemId, i.ParentId, t.Amount
from Items i join Transactions t on i.ItemId = t.ItemId
union all
select i.itemId, i.ParentId, t.Amount
from Items i join cteAggregateCost c on i.ItemId = c.ParentId
join Transactions t on i.ItemId = t.ItemId
)
select i.ParentId, i.ItemId, i.ItemName, sum(Amount) As AggregateCost
from Items i left join cteAggregateCost c on i.ItemId = c.ItemId
group by i.ParentId, i.ItemId, i.ItemName
This is the result I am getting:
And as a result, I hope to get:
As you can see, all lines work except the first two, which have no cost for the container, only the elements it contains.
Thanks so much for any direction you might suggest!
source to share
You can use recursive CTE
like this
;WITH temp AS
(
SELECT i.*, sum(isnull(t.Amount,0)) AS Amount
FROM @Items i
LEFT JOIN @Transactions t ON t.ItemId = i.ItemId
GROUP BY i.ItemId, i.ParentId, i.ItemName
)
,cteAggregateCost
as
(
select i.ItemId, i.ItemId AS RootId, i.Amount
from temp i
union all
select i.ItemId, c.RootId, i.Amount
from cteAggregateCost c
INNER JOIN temp i ON i.ParentId = c.ItemId
)
select i.*, ca.TotalAmount
from @Items i
CROSS APPLY
(
SELECT Sum(cac.Amount) AS TotalAmount
FROM cteAggregateCost cac WHERE i.ItemId = cac.RootId
) ca
OPTION (MAXRECURSION 0)
Demo link: http://rextester.com/XMK96314
source to share
Thank you TriV. Your answer is brilliant! However, I found a simpler answer posted by Xi Jin on the SQL Server Forum . Here's his solution:
With cteAggregateCost
as
(
select i.itemId as rootid,i.itemid, i.ParentId
from Items i
union all
select rootid, i.itemId, i.ParentId
from Items i join cteAggregateCost c on i.ParentId = c.ItemId
)
select a.parentid, a.ItemId , a.ItemName , sum(t.Amount) As AggregateCost
from items a
left join cteAggregateCost i on a.itemid = i.rootid
left join Transactions t on i.ItemId = t.ItemId
group by a.parentid, a.ItemId, a.ItemName
Both solutions give the same correct results that I was looking for when validating on a much larger dataset with many levels of hierarchical relationships. For me, Xi Lin's answer is easier to understand. I just couldn't figure out how to add the rootID technique, which retained the values of the elements that had no value, and only had costs from the children.
source to share