Hi Raja Shekar,
I assume you have a table Orders with following data.
ITEM ORDERNO QTY
--------------------
X ORdx 10
X Ordy 20
X Ordx 30
Y Ordx 20
Y Ordy 30
X OrdX 40
X OrdZ 50
Z ORdZ 100
Y Ordzy 8
Y OrdXy 9
X Ordxys 10
If you want to get a result set like the following,
ITEM QTY
-----------------
X 60
Y 50
X 90
Z 100
Y 17
X 10
then try the following query on table Orders
WITH OrderCTE As (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) RowNum, ITEM,QTY FROM ORDERS
)
,ORDERQTYCTE AS
(
SELECT TOP 1 0 AS RowNumber,ITEM as Item,CAST(0 as Real) as Qty, 0 OrderLevel
FROM OrderCTE
UNION ALL
SELECT RowNumber+1 AS RowNumber, OrderCTE.ITEM As Item,
(CASE WHEN OrderCTE.ITEM = ORDERQTYCTE.Item THEN ORDERQTYCTE.QTY+OrderCTE.QTY
ELSE OrderCTE.QTY END) AS Qty,
CASE WHEN OrderCTE.ITEM <> ORDERQTYCTE.Item THEN OrderLevel+1
ELSE OrderLevel END As OrderLevel
FROM OrderCTE INNER JOIN ORDERQTYCTE
ON ORDERQTYCTE.RowNumber = OrderCTE.RowNum - 1
)
SELECT Item,MAX(QTY) QTY FROM ORDERQTYCTE
GROUP BY OrderLevel,Item ORDER BY OrderLevel
Here have used CTE, Recursive CTE to get the desired result set.
I hope this solution helps you. Happy coding. :)