select * from emp
--OUTPUTS
id name adddress
7 m a
8 k l
4 j NULL
10 h g
2 k n
14 NULL mm
9 h b
DELETE t FROM
(SELECT id ,RANK() OVER(PARTITION BY name ORDER BY id DESC) AS rank,name FROM emp) AS t
WHERE t.rank <> 1
DELETE T FROM
(SELECT Row_Number() Over(Partition BY [Name] ORDER BY [ID]) AS RowNumber,* FROM emp)T
WHERE T.RowNumber > 1
DELETE
FROM emp
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM emp
GROUP BY name)
SELECT name, COUNT(*) TotalCount
FROM emp
GROUP BY name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
--OUTPUTS
name totalcount
h 2
k 2
--it is use when name or address are null then display nonnull value
select id,coalesce(name,address) address from emp
--OUTPUTS
id address
7 m
8 k
4 j
10 h
14 mm
--pivot value and sum of id here h and k is data of name but they became columm
SELECT h AS h, k AS k
FROM
(SELECT id, name
FROM emp ) ps
PIVOT
(
SUM (id)
FOR name IN
( [h], [k])
) AS pvt
--OUTPUTS
columm name h k
data 10 8
--display , between two value or more here all address data display with comma seprated
DECLARE @name VARCHAR(1000)
SELECT @name = COALESCE(@name,'') + address + ';'
FROM emp group by address
SELECT @name AS DepartmentNames
--OUTPUTS
DepartmentNames
a;g;l;mm;
