How to combine rows in SQL Server if Identical values exist, such as a given product name?

Question

As stated in the title, I'm using SQL Server.

This is my current query

select  
    p.[Name], p.[Price], prli.Quantity, (p.Price * prli.Quantity) as subtotal
from 
    PurchaseRequests as pr
join 
    PurchaseRequestLineItems as prli on pr.Id = prli.PurchaseRequestId
join 
    Products as p on prli.ProductId = p.ID
where 
    p.VendorID = 2 and pr.Status = 'Approved';

Let's say my results look like this

How do I get my query to instead combine box of paper every time it occurs so instead of having a row for each time there is a valid order for that product, it instead combines it into a single line?

I figured it out using Linq, but I want to make a stored procedure to make it a little more efficient.

Linq syntax below:

There are multiple ways to skin the cat here, this was just how I first figured it out. I also just learned about using IQueryable to make it faster, but I really want to make a stored procedure, mostly for the practice.

 public JsonRequest All(int? Id)
 {
     JsonRequest json = new JsonRequest();
     VendorOrder vendorOrder = new VendorOrder();
     vendorOrder.Prlis = db.PurchaseRequestLineItems.Where(li => li.Products.VendorID == Id && li.PurchaseRequest.Status == "Approved").ToList();
     vendorOrder.Total = vendorOrder.Prlis.Sum(li => li.Quantity * li.Products.Price);

     List<string> ProductNames = new List<string>();
     List<string> ProductPartNum = new List<string>();
     List<decimal> Quantity = new List<decimal>();
     List<decimal> Price = new List<decimal>();
     List<PurchaseRequestLineItem> lineItems = new List<PurchaseRequestLineItem>();

     string partNum = "";
     string name = "";

     foreach (var product in vendorOrder.Prlis)
     {
         name = product.Products.Name;
         partNum = product.Products.Partnum;

         if (!ProductPartNum.Contains(partNum))
         {
             var x =  vendorOrder.Prlis.FindAll(p => p.Products.Partnum == partNum).ToList();
             ProductNames.Add(name);
             ProductPartNum.Add(partNum);
             Quantity.Add(x.Sum(p => p.Quantity));
             Price.Add(x.Sum(p => p.Quantity * p.Products.Price));
         }
     }

     vendorOrder.Quant = Quantity;
     vendorOrder.Cost = Price;
     vendorOrder.productName = ProductNames;
}

Answer 1

You need a GROUP BY clause. This will give one row per item, but if that item has multiple different prices, each [name + price] will be on a separate row:

SELECT  p.[Name] ,
        p.[Price] ,
        Quantity = SUM(prli.[Quantity]) ,
        Subtotal = SUM(( p.[Price] * prli.[Quantity] ))
FROM    PurchaseRequests AS pr
        JOIN PurchaseRequestLineItems AS prli ON pr.Id = prli.PurchaseRequestId
        JOIN Products AS p ON prli.ProductId = p.ID
WHERE   p.VendorID = 2
        AND pr.Status = 'Approved'
GROUP BY [Name], [Price] ;