How to display products under Category in sql in a table

Question

I have the following table:

where the products are in different categories and i am excepting the output:

like product and its cost need to be displayed under category(For category cost value i want to display total products cost) .I tried with different approaches by using roll up and grouping , but i am not getting excepted output.

Answer 1

Here it goes:

Sample Data:

CREATE TABLE #product (ID INT, Category VARCHAR(50), Product VARCHAR(50), Value INT)
INSERT INTO #product
VALUES(1,'Non-veg','Chicken',150),
(2,'Non-veg','Mutton',200),
(3,'Non-veg','Fish',220),
(4,'Non-veg','Prawns',250),
(5,'Veg','Gobi',100),
(6,'Veg','Parota',45),
(7,'Veg','vegbirani',150) 

Query using GROUP BY with ROLLUP

SELECT  Category, Product,
       SUM(Value) AS Value
FROM #product
GROUP BY Category, Product WITH ROLLUP

Results:

you can further manipulate the results:

SELECT  COALESCE(product,category,'Total') Category,
       SUM(Value) AS Value
FROM #product
GROUP BY Category, Product WITH ROLLUP

Result:

To answer the comment below: "is there any way to display Category first then Products" this seemed to work:

;WITH CTE AS (
SELECT  Category, Product,
       SUM(Value) AS Value,
      ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Product  ) AS rn
FROM #product
GROUP BY Category, Product WITH ROLLUP)

SELECT  Category = COALESCE(A.product,A.category,'Total') , A.Value 
FROM CTE AS A 
ORDER BY ISNULL(A.category,'zzzzzz') ,rn

Results: