Hello Good Morning.
I have Table in that there is 15th column i want to merge duplicate rows in one row and then delete that duplicate row after keeping one row as record
My Table like that
id name email address city state country salary ....
1 a [email protected] null null a xyx null
2 null [email protected] a a null null 10000
You could group on email and update all columns with the groupwise maximum:
declare @t table (id int, name varchar(50), email varchar(50),
address varchar(50), city varchar(50), state varchar(50),
country varchar(50), salary int)
insert @t
values (1, 'a', '[email protected]', null, null, 'a', 'xyx', null),
(2, null, '[email protected]', 'a', 'a', null, null, 10000)
-- Update all rows for an email address
update t
set name = combined.name
, address = combined.address
, city = combined.city
, state = combined.state
, country = combined.country
, salary = combined.salary
from @t t
join (
select
max(name) as name
, email
, max(address) as address
, max(city) as city
, max(state) as state
, max(country) as country
, max(salary) as salary
from @t
group by
email
) combined
on combined.email = t.email
Then delete all duplicate rows:
-- Delete duplicate rows
delete t
from @t t
join @t t2
on t2.email = t.email
and t2.id < t.id
-- Display result
select * from @t
This prints:
id name email address city state country salary
1 a [email protected] a a a xyx 10000