I'm new to SQL Server, and I'm trying to remove duplicates from a table but with some conditions, and my doubt is on how to apply these conditions to the query.
I need to remove duplicates from the Users table, eg:
Id Code Name SysName
-----------------------------
1 D1 N1
2 D1
3 D1 N1 N-1
4 E2 N2
5 E2 N2
6 E2 N2
7 X3
8 X3 N-3
9
10
11 Z4 W2 N-4-4
12 Z4 W2 N-44
In the above table: for D1 code I want to keep the ID=3, which has all columns filled (Code, Name, and SysName) and delete ID=1 and ID=2
For E2 code, I want to keep any of these and remove the two duplicated ones
For X3 code, keep the one which has SysName=N-3
For ID=9, ID=10 (empty code and everything empty, remove all)
For Z4 code, remove ID=11 and keep N-44 Sysname
And the last thing, I've a FK with other table, so I think that I need first to get all Id's from Users, delete these ids from the second dependent table and finally delete from Users table.
Do you have any idea on how to achieve it? I do not pretend the solution but a structure code or examples/scenarios you have similar to it, any suggestion would be fine for me.
EDIT:
To resume.. I have Users table:
Id Code Name SysName
-----------------------------
1 D1 N1
2 D1
3 D1 N1 N-1
4 E2 N2
5 E2 N2
6 E2 N2
7 X3
8 X3 N-3
9
10
11 Z4 W2 N-4-4
12 Z4 W2 N-44
And I want to keep only:
Id Code Name SysName
-----------------------------
3 D1 N1 N-1
4 E2 N2
8 X3 N-3
12 Z4 W2 N-44
Are you looking for something like
SELECT Code,
MAX(ISNULL(Name, '')) Name,
MAX(ISNULL(SysName, '')) SysName
FROM T
WHERE Code IS NOT NULL
GROUP BY Code;
Returns:
+------+------+---------+
| Code | Name | SysName |
+------+------+---------+
| D1 | N1 | N-1 |
| E2 | N2 | |
| X3 | | N-3 |
| Z4 | W2 | N-4-4 |
+------+------+---------+