shashi kant (7/31/2012)
Hi SQLNavie,I achived the solution using Cursor query. Please find below query
Declare @tempTable as table(id int, value Int)
Declare @value as int
DECLARE @CUR AS CURSOR
SET @CUR = CURSOR FOR
Select distinct ID from taba where parentid is null
OPEN @CUR
FETCH NEXT FROM @CUR INTO @value
WHILE @@FETCH_STATUS =0
BEGIN
insert into @temptable (id, value)
select @value, SUM(value) Value from tabb where ID in(Select ID from taba a where parentid = @value or a.ID = @value)
FETCH NEXT FROM @CUR INTO @value
END
CLOSE @CUR
DEALLOCATE @CUR
Select * from @tempTable
Please note that cursors have terrible performance, especially when a set-based solution can be used, such as in this case.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP