SQL how to remove duplicate results from same table

Question

I created an sql table on visual studio for visual basic, where i can put dynamic data every time i click on a button. I get the table results from textboxes and one datagridview with values(quantity,price,discount). I use the "command insert values into" inside a for loop to be able to get all the datagridview results. The problem is that the table has rows with duplicate values. I want to remove the duplicate values and keep only the values from datagridview e.g I have a table with the following format:

enter image description here

• Dan Guzman: I suggest you perform formatting for display purposes in the app code rather than T-SQL.

Answer 1

Following code should work, tested in Memory table:

DECLARE @TBL AS TABLE
( 
 ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
 First VARCHAR(10),
 Last VARCHAR(10),
 Address VARCHAR(30),
 Quantity INT,
 Price INT,
 Discount VARCHAR(10)
);
INSERT @TBL (First, Last, Address, Quantity, Price, Discount )
 VALUES 
 ('rolp', 'Gaflp', '24 street', 1, 155, '10%'),
 ('rolp', 'Gaflp', '24 street', 3, 202, '5%'),
 ('rolp', 'Gaflp', '24 street', 4, 312, '6%');
WITH CTE AS (
 SELECT *, ROW_NUMBER() OVER (PARTITION BY First, Last, Address ORDER BY Quantity) AS Row
 FROM @TBL
 )
SELECT ID, 
 CASE WHEN Row = 1 THEN First ELSE '' END AS First,
 CASE WHEN Row = 1 THEN Last ELSE '' END AS Last,
 CASE WHEN Row = 1 THEN Address ELSE '' END AS Address,
 Quantity, Price, Discount
 FROM CTE;