How to display products under Category in sql in a table

Question

I have the following table:

product

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

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.

Show us what you have tried and where you fail. It genereally helps, if your read the [FAQ] and [ask] first. If it is possible, create a http://sqlfiddle.com/ for us, so we can give you some hands on results — Marco, Feb 16 '16 at 20:27

score 3 · Answer 1 · answered Feb 16 '16 at 20:27

3

Using Rollup you would do it like this.

SELECT  COALESCE(product,category,'Total') Category,
        SUM(VALUE) cost
FROM    products
GROUP BY ROLLUP(category,product)

answered Feb 16 '16 at 20:27

JamieD77

13,796
1
17
27

Fuzzy · Accepted Answer · 2016-02-18T17:30:28.063

2

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:

edited Feb 18 '16 at 17:30

answered Feb 16 '16 at 20:28

Fuzzy

3,810
2
15
33

1

I (personally) believe that the inclusion of sample data - as you have done here - or at least an [SQLfiddle](http://sqlfiddle.com/) should be made mandatory for such questions and should be provided by the one asking the question. But great that you did so in your answer. – Ralph Feb 16 '16 at 20:33
@Ralph totally agree with you there – Fuzzy Feb 16 '16 at 20:35
1

You could add a `CASE WHEN category is null or product is null THEN 1 ELSE 0 as BOLD_INDICATOR` to let the display know when to use bold. – Hogan Feb 16 '16 at 21:26
1

Thanks @kamran ,is there any way to display Category first then Products – chandra Feb 18 '16 at 17:16
@chandra I just added the solution to my answer. – Fuzzy Feb 18 '16 at 17:30
1

Thank you very much @KamranFarzami – chandra Feb 18 '16 at 17:57
@chandra you're welcome. if my answer answered your question feel free to selected as the answer. – Fuzzy Feb 18 '16 at 17:58

score 0 · Answer 3 · answered Feb 16 '16 at 20:30

0

Maybe something like this... doesn't give your exact output but it's close...

Select category, product, sum(value) as value
From TableName
group by grouping sets ((category),(category, product))

answered Feb 16 '16 at 20:30

xQbert

34,733
2
41
62

How to display products under Category in sql in a table

3 Answers3

Linked