Hello All,

I have a below table,

DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ParentItemName, ItemOrder, ReportId)
VALUES('Item1', NULL, 1, 5),('Item1-Child1', 'Item1', 0, 5),('Item1-Child2', 'Item1', 0, 5),('Item2', NULL, 2, 5),
('Item11', NULL, 1, 6),('Item12', NULL, 2, 6),('Item12-Child1', 'Item12', 0, 6),('Item13', NULL, 3, 6)
SELECT * FROM @TBL

Here,

1. for all ReportId, child items's ItemOrder  = 0

2. example, for ReportId = 5, both child items ("Item1-Child1" & "Item1-Child1") of parent "Item1" has ItemOrder = 0

I need to,

1. update all child items with ascending numbers starts with 1 against each parent and each report.

2. for each different parent or different report, order by should starts with 1 again.

I think, CURSOR can solve my issue, but I am looking for some other solution, Please suggest, Thanks!!!

Output should be,

try this..you do not need cursor.. you missed couple of values in insert to match the outpput...added those to match the output...

DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ParentItemName, ItemOrder, ReportId)
VALUES('Item1', NULL, 1, 5),('Item1-Child1', 'Item1', 0, 5),('Item1-Child2', 'Item1', 0, 5),
('Item2', NULL, 2, 5),('Item3', NULL, 3, 5),('Item3-Child1', 'Item3', 0, 5),('Item3-Child2', 'Item3', 0, 5),
('Item11', NULL, 1, 6),('Item12', NULL, 2, 6),('Item12-Child1', 'Item12', 0, 6),('Item13', NULL, 3, 6)

;with cte as (SELECT *,ROW_NUMBER() over(partition by ParentItemName,reportid order by Itemid) As R1 FROM @TBL
where Itemorder=0)

update A set 
ItemOrder = R1
from @tbl A inner join cte b on a.ItemId=b.ItemId

select * from @tbl