I have the following table:

I would like to merge Other, Misc and Extra categories as "Other" category. Qty and Price can have sum of Other, Misc and Extra categories.

One of the ways is to:

-- Create temp table to hold sum of Other, Misc, Extra

DECLARE @Qty AS INT, @Price AS INT

SELECT @Qty = Sum(Qty), @Price = Sum(Price) 
FROM Product 
WHERE ProductCategory IN ('Other', 'Extra', 'Misc')

DELETE FROM Product 
WHERE ProductCategory IN ('Other', 'Extra', 'Misc')

INSERT INTO Product (ProductCategory, Qty, Price) 
VALUES ('Extra', @Qty , @Price)

What is the easiest way to do this using SQL?

CodePudding user response：

You could do this using a case epxression with group by

select v.ProductCategory, Sum(qty) Qty, Sum(price) Price
from t
cross apply (values(
    case when productcategory in ('Misc','Extra') then 'Other' /*or Extra...?*/
    else ProductCategory 
  end)
)v(ProductCategory)
group by v.ProductCategory

Example fiddle

CodePudding user response：

Using an OUTPUT to a temp table

declare @tmp table(Qty int, Price int);

delete tbl
  output deleted.qty, deleted.price into @tmp (Qty, Price)
where ProductCategory in ('Misc','Other');

update t
  set Qty = t.Qty   u.qty , Price = t.Price   u.Price
from tbl t
join (select sum(Qty) qty, sum(Price) Price
      from @tmp) u
      on t.ProductCategory = 'Extra';

db<>fiddle