How to merge rows in a table into one row that have the same certain columns?

Question

SQL

For example, the table below has data that has the same ACC_ID and NAME columns. I need to merge all such records into one. In the database, in my case, there are always 2 of them, that is, no more than 2 duplicates are always displayed for a certain key, in which only the IDENTITY column differs. There are other entries that do not have duplicates. In my example, the same is true. I would also like to not just delete duplicates, but it’s better to merge data from them. How can I do that?

IF OBJECT_ID('dbo.People') IS NULL
CREATE TABLE dbo.People (
  USER_ID INT NOT NULL
 ,ACC_ID INT NOT NULL
 ,NAME NVARCHAR(30) NOT NULL
 ,DEPARTMENT_ID INT NULL
 ,CONSTRAINT PK_People_USER_ID PRIMARY KEY CLUSTERED (USER_ID)
) ON [PRIMARY]
GO

INSERT INTO dbo.People(USER_ID, ACC_ID, NAME, DEPARTMENT_ID)
VALUES 
(1, 10, N'Владимир', 1),
(2, 10, N'Владимир', 1),
(3, 30, N'Александр', 2),
(4, 30, N'Александр', 2),
(5, 50, N'Юрий', 25),
(6, 50, N'Юрий', NULL),
(7, 60, N'Андрей', NULL),
(8, 60, N'Андрей', NULL);
GO

In the end I want to get this:

(2, 10, N'Владимир', 1),
(4, 30, N'Александр', 2),
(6, 50, N'Юрий', 25),
(8, 60, N'Андрей', NULL);

UPDATE :
There are still non-duplicate rows in the table. That is, not every entry has a pair that needs to be either simply deleted or merged into one.
I get duplicates like this:

SELECT COUNT(*) AS [Count], ACC_ID, NAME 
  FROM People 
  GROUP BY ACC_ID, NAME 
  HAVING COUNT(*) > 1

Answer 1

there are duplicate rows in the database, but they need to be cleaned up.

First you need to remove the "dirty" data. For example,

(5, 50, N'Юрий', 25),
(6, 50, N'Юрий', NULL),

WHILE NOT (SELECT TOP (1) COUNT(ACC_ID) FROM People GROUP BY ACC_ID HAVING (COUNT(ACC_ID) > 1)) IS NULL  BEGIN
  DELETE FROM People 
    WHERE (USER_ID IN ( SELECT MAX(USER_ID) FROM People GROUP BY ACC_ID HAVING (COUNT(ACC_ID) > 1)))
END