How can we add two colmn in 1 column

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'