1

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

  • I suggest you perform formatting for display purposes in the app code rather than T-SQL. – Dan Guzman Jan 2 '19 at 13:08
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.