Clever Geek Handbook

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


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


source


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


source


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


source






More articles:


All Articles