Dynamically group by with “Group Header”

问题:

Could someone help me with this problem. I have a table with Products, ProductsSales. Here are some screenshots of them.

Now I would like to make a TSQL query which can group them and add something like a "header description"? Something like this

It would be perfect, if it is a relatively dynamically query.

Could someone help me here or give me some tipps?

回答1:

Try this one:

SELECT DISTINCT A.ProductName, B.Price 
FROM Product A 
INNER JOIN Product_Sales B ON A.ProductId = B.ProductId
GROUP BY ProductName, Price

回答2:

You can use GROUP BY with GROUPING SETS in SQL Server to get the desired output.

SELECT CASE 
         WHEN groupname IS NOT NULL THEN groupname 
         WHEN groupname IS NULL 
              AND productname IS NULL THEN 'food' 
         ELSE productname 
       END          AS GroupName, 
       Sum(s.price) price 
FROM   product p 
       INNER JOIN TableSales s 
               ON p.productid = s.productid 
GROUP  BY grouping sets( ( p.productname ), ( p.groupname ), ( ) ) 

Online Demo

Output

+------------+-------+
| GroupName  | price |
+------------+-------+
| fruit      | 14    |
+------------+-------+
| vegetables | 52    |
+------------+-------+
| food       | 66    |
+------------+-------+
| Apple      | 5     |
+------------+-------+
| Capsicum   | 35    |
+------------+-------+
| Cucumber   | 17    |
+------------+-------+
| Orange     | 9     |
+------------+-------+

回答3:

I would be inclined to unpivot the categories and then aggregate:

select which, name, sum(price)
from products p cross apply
     (values ('productName', productName),
             ('groupName', groupName),
             ('parentGroup', parentGroup)
     ) v(which, name) left join
     productsales ps
     on ps.productId = p.productId
group by which, name;