Remove the duplicate entry, but keep the link
I have a table that looks like this:
Name | Product | Total
---- --------- ------
A Toy $5
A Car $30,000
A Equipment $500
B Car $100,000
etc...
I would like to remove duplicate names, but keep the link to the product example:
Name | Product | Total
---- --------- ------
A Toy $5
Car $30,000
Equipment $500
B Car $100,000
Any ideas?
+3
Mark manickaraj
source
to share
3 answers
I assumed what you want to do Name = ''
is because you cannot delete duplicate entries.
So you can use ROW_NUMBER
inside WITH
and update the fieldName
;WITH C AS(
SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) AS Rn
,Name
,Product
,Total
FROM yourTable
)
UPDATE C
SET Name = ''
WHERE Rn <> 1
0
sqluser
source
to share
If you want to save Name
only when Product ='Toy'
, then use the operatorCase
Select case when Product ='Toy' then Name else '' END as Name,
Product,
Total
From yourtable
0
P เฎฐเฎคเฏเฎชเฏ
source
to share
try it
WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY i.name),
i.name,i.product,i.Total
FROM yourtable i
)
SELECT
case when prev.name = cte.name
then
' '
else
cte.name
end as name,
cte.address,
cte.Total
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
0
SimarjeetSingh Panghlia
source
to share