Very simplistically ...
You can do this by running a query that gets all of the category X and Y rows, a separate query that gets the category Z rows and union them together. You don't want to group by category, you will need to group by the Item Name. For example:
SELECT [Item Name],Sum(Qty) AS QTY ,Sum(Price) AS PRICE,'X+Y' as Category
FROM test
WHERE Category IN ('X','Y')
GROUP BY [Item Name]
UNION ALL
SELECT [Item Name],Sum(Qty),Sum(Price),'Z' as Category
FROM test
WHERE Category = 'Z'
GROUP BY [Item Name]
Which gives these results
ITEM NAME QTY PRICE CATEGORY
ABC 30 500 X+Y
ABC 40 900 Z
However, seeing your comment
Quote:
We know that total qty of a item which is purchase (credit and deposite (in 1 column)) and cash for seperate colmn.
then it seems that you want the results further combined into something like
ITEM NAME QTYXY PRICEXY QTYZ PRICEZ
ABC 30 500 40 900
so you can join the results of the query above to itself based on [Item Name] to get both sets of results in a single row e.g.
WITH TempTable AS (
SELECT Item,Sum(Qty) AS QTY ,Sum(Price) AS PRICE,'X+Y' as Category
FROM test
WHERE Category IN ('X','Y')
GROUP BY Item
UNION ALL
SELECT Item,Sum(Qty),Sum(Price),'Z' as Category
FROM test
WHERE Category = 'Z'
GROUP BY Item
)
SELECT T1.ITEM, T1.QTY AS QtyXY, T1.PRICE AS PriceXY,
T2.QTY as QtyZ, T2.PRICE as PriceZ
FROM TempTable T1
INNER JOIN TempTable T2 ON T1.Item=T2.Item AND T2.Category='Z'
WHERE T1.Category = 'X+Y'