if null exists

Question

I have a large database with several tables like this.

CREATE TABLE [dbo].Test ON [PRIMARY]
GO
INSERT [dbo].[Test] ([product], [price]) VALUES (N'hat', 5)
INSERT [dbo].[Test] ([product], [price]) VALUES (N'collar', 2)
INSERT [dbo].[Test] ([product], [price]) VALUES (N'collar', NULL)
INSERT [dbo].[Test] ([product], [price]) VALUES (N'boots', NULL)
INSERT [dbo].[Test] ([product], [price]) VALUES (N'boots', 15)
INSERT [dbo].[Test] ([product], [price]) VALUES (N'shirt', 20)
INSERT [dbo].[Test] ([product], [price]) VALUES (N'pants', 40)
INSERT [dbo].[Test] ([product], [price]) VALUES (N'necklace', NULL)
INSERT [dbo].[Test] ([product], [price]) VALUES (N'ring', NULL)
GO

This is what the data looks like.

I need it to look like this.

Can this be done with a something like a case statement? I need a light solution so it's not a big performance hit.

Answer 1

If you need a SELECT:

select product, price  
from Test  
except  
select product, NULL  
from Test  
where price is not null  

If you need a DELETE:

delete t  
from Test t  
where price is null   
and exists (select * from Test where product = t.product and price is not null)  

Answer 2

I would separate out the items which have a price and items which don't have a price, then combine them.

   ;WITH items_missing_price AS (  
     -- find which items are missing a price by name  
     SELECT DISTINCT name FROM items GROUP BY name HAVING MAX(Price) IS NULL  
   )  
   \-- select the items which have a price  
   SELECT id, name, price FROM items WHERE price IS NOT NULL  
     
   UNION  
     
   \-- add the items which do not have a price based on name  
   SELECT i.id, i.name, NULL FROM items i  
   INNER JOIN items_missing_price mp ON i.name = mp.name;  

Here's a SQL Fiddle with an example,
http://sqlfiddle.com/#!18/79608/7

Note: If I were working on this data in production, I would take the product and separate it from the price if there are multiple prices for a product. E.g.,

   CREATE TABLE dbo.products (  
     id INT PRIMARY KEY IDENTITY(0, 1),  
     name VARCHAR(max)  
   );  
     
   CREATE TABLE dbo.product_prices (  
     id INT PRIMARY KEY IDENTITY(0, 1),  
     product_id INT NOT NULL FOREIGN KEY REFERENCES dbo.products(id),  
     price FLOAT NOT NULL  
   );  
     
   INSERT INTO dbo.products (name) VALUES ('Socks');  
   INSERT INTO dbo.products (name) VALUES ('Shoes');  
   INSERT INTO dbo.products (name) VALUES ('Hat');  
   INSERT INTO dbo.products (name) VALUES ('Pants');  
     
   INSERT INTO dbo.product_prices  
   SELECT p.id, 5.99  
   FROM dbo.products p   
   WHERE p.name = 'Socks';  
     
   INSERT INTO dbo.product_prices  
   SELECT p.id, 49.99  
   FROM dbo.products p   
   WHERE p.name = 'Shoes';  
     
   INSERT INTO dbo.product_prices  
   SELECT p.id, 9.99  
   FROM dbo.products p   
   WHERE p.name = 'Hat';  

Then you could query everything like this,

   SELECT p.id, p.name, pp.price FROM dbo.products p  
   LEFT OUTER JOIN dbo.product_prices pp ON p.id = pp.product_id  



   | id |  name |  price |  
   |----|-------|--------|  
   |  0 | Socks |   5.99 |  
   |  1 | Shoes |  49.99 |  
   |  2 |   Hat |   9.99 |  
   |  3 | Pants | (null) |  

Here's a SQL Fiddle describing the approach above,
http://sqlfiddle.com/#!18/ae487/2