I have a table with the following data:

Table1

Using the key UniqueKey I want to add the values in the columns Value 1 and Values 2 so that each Unique Key exists only once in my table.

The Table1 should then look like this:

I have already made the following considerations. I can use the group by and sum command to add the columns as desired. I have trouble saving these changes in the table and deleting the redundant columns. So far I have only used the merge command on two different tables.

SELECT UniqueKey, SUM(Value1) Value1, SUM(Value2) Value2
  FROM Table1
 GROUP BY UniqueKey

Has anyone an idea?

CodePudding user response：

You will need to group by the columns the other 2 columns Text A and Text B.

SELECT UniqueKey, [Text A], [Text B], SUM([Value 1]) AS Val1, SUM([Value 2]) AS Val2
FROM Table1
GROUP BY UniqueKey, [Text A], [Text B]

If you want to get this data into Table1, you can save the result of the query above in a temp table, delete the data in Table1, then re-insert the data from the temp table into Table1.

-- Save the data into temp table #Table1
SELECT UniqueKey, [Text A], [Text B], SUM([Value 1]) AS Val1, SUM([Value 2]) AS Val2 INTO #Table1
FROM Table1
GROUP BY UniqueKey, [Text A], [Text B]
GO

-- Delete the data in Table1
DELETE FROM Table1
GO

-- Re-insert the data from the temp table #Table1 into Table1
INSERT INTO Table1 (UniqueKey, [Text A], [Text B], [Value 1], [Value 2])
SELECT UniqueKey, [Text A], [Text B], [Value 1], [Value 2]
FROM #Table1
GO

CodePudding user response：

You can create a new table with the current query

SELECT [UniqueKey], [Text A], [Text B], SUM([Value 1]) AS Value1, SUM([Value 2]) AS Value2
  INTO Table2
  FROM Table1
 GROUP BY [UniqueKey], [Text A], [Text B]

drop the original source

DROP TABLE Table1

rename new one to the original

sp_rename 'Table2', 'Table1'

and add a primary key on UniqueKey column to enforce uniqueness as the last step

ALTER TABLE Table1 ADD PRIMARY KEY (UniqueKey);