CODEWITHSUNDEEP
sql-servermerge
Natasha
Natasha

Reputation: 35

Merge rows in single SQL table

I have one table with rows like 

ID | Key   | Value1 | Value2 | Type  
55 | 012018| 0      | 0      |  1   
55 | 012018| 50     | 10     | 1

I need to update this table to remove these duplicates so that my ID, KEY and Type is matching and Value1 and Value2 are added.

Get a result as 

ID | Key   | Value1 | Value2 | Type  
55 | 012018| 50     | 10     |  1

Upvotes: 1

Views: 69

Answers (2)

Admir
Admir

Reputation: 155

You can use temp table to store your calculated values, delete them from your table by joining on Id, Key, Type and reinserted them. This way you will get distinct values in table and remove duplicates. I've provided example how you can do that.

Note: I've placed sql code into transaction and commented commit part, so you can easily test it.

BEGIN TRAN PrototypeExample

-- create temp table where we will store calculated data
CREATE TABLE #tempValues(
    Id INT,
    [Key] INT,
    [Type] INT,
    Value1 INT,
    Value2 INT
)

-- insert calculated values into temp table
INSERT INTO 
    #tempValues
    (
        Id, 
        [Key], 
        [Type], 
        Value1, 
        Value2
    )
SELECT 
    e.Id, 
    e.[Key], 
    e.[Type], 
    SUM(e.Value1) Value1, 
    SUM(e.Value2) Value2
FROM 
    example e
GROUP BY 
    e.Id,
    e.[Key],
    e.[Type]

-- show data
SELECT * FROM #tempValues

-- delete data from my table 
DELETE 
    e 
FROM 
    example e
INNER JOIN 
    #tempValues t 
    ON 
        e.Id = t.Id 
        AND 
        e.[Key] = t.[Key] 
        AND 
        e.[Type] = t.[Type];

-- insert data from temp table
INSERT INTO
    example
    (
        Id, 
        [Key], 
        [Type], 
        Value1, 
        Value2
    )
SELECT
    t.Id,
    t.[Key],
    t.[Type],
    t.Value1,
    t.Value2
FROM
    #tempValues t

-- new data populated
SELECT * FROM example

-- delete temp table
IF OBJECT_ID('tempdb..#tempValues') IS NOT NULL DROP TABLE #tempValues

-- for testing
ROLLBACK TRANSACTION PrototypeExample

-- if you find it useful, commit
-- COMMIT TRANSACTION

Upvotes: 0

Michael Entin
Michael Entin

Reputation: 7744

I think you want to simply group them by ID, Key, Type

SELECT ID, Key, SUM(Value1) AS Value1, SUM(Value2) AS Value2, Type
FROM TABLE
GROUP BY ID, Key, Type

Upvotes: 1

Related Questions